Need help with an Excel VBA ado query

[ncarty97]

Howdy!

First off, thanks for any help that can be provided. I'm a bit stuck. I've been using Excel/VBA with and SQL server for a while, but due to a new job, I am having to use an Access database for some data.

Here are a few stats on my setup:

Excel 2000
Access 2003
Using ADO 2.7 Library

Ok here is what I am trying to do in VBA. Seems pretty simple to me, but I can't get it to work:

I open up a new ado connection to my database, then I generate an SQL statement, then open the recordset.

Here is the VBA:


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

cn.ConnectionTimeout = 500
cn.Open (imoDB) ' where IMO is a global const of my database connection string
cn.CommandTimeout = 500

strSQL = ""
strSQL = "SELECT A.lhu_id, A.ticker, A.book_currency, A.instrument_type, " strSQL = strSQL & "A.mkt_price, A.under_ticker, A.firm_name, A.expiry, A.strike, B.description, Sum(A.position) AS SumOfposition "
strSQL = strSQL & "FROM position_mapping AS B RIGHT JOIN imo_positions AS A ON B.ticker = A.ticker "
strSQL = strSQL & "GROUP BY A.desk_id, A.lhu_id, A.ticker, A.book_currency, A.instrument_type, A.mkt_price, A.under_ticker, "
strSQL = strSQL & "A.firm_name, A.expiry, A.strike, B.description "
strSQL = strSQL & ""HAVING (((A.desk_id)=""MGMT"") AND ((A.lhu_id)=""MGMTT"") AND ((Sum(A.position))<>0));"

rs.Open (strSQL), cn

When I try to do the Open function, I get this error:

"Method 'Open' of object '_Recordset' failed"

However, when I take the SQL query, as generated, and paste it into a blank query in access, it works fine!

So it seems to me that something I am trying to pass through via ADO just doesn't work in ADO, but I can't figure it out!

Please help!

Also, on another note, I have a bad reference to a library in my Excel VBA. When I try to redirect it to the correct dll file, sometime it works, sometimes not, and almost never does it keep the correct reference. It just reverts the next time and I have to do it again. Any ideas how to fix that?

Thanks again.

[Bob Phillips]

If it were I, I would do 2 things

1. Build a query it by bit but directly in Access to see what works and where it fails, Access SQL is difefrent

2. Ask questions in the Access forum, they might be more attuned than us

The SQL string at least should be able to be simplified

strSQL = "SELECT A.lhu_id, A.ticker, A.book_currency, A.instrument_type, " & _
"A.mkt_price, A.under_ticker, A.firm_name, A.expiry, A.strike, B.description, Sum(A.position) AS SumOfposition " & _
"FROM position_mapping AS B RIGHT JOIN imo_positions AS A ON B.ticker = A.ticker " & _
"GROUP BY A.desk_id, A.lhu_id, A.ticker, A.book_currency, A.instrument_type, A.mkt_price, A.under_ticker, " & _
"A.firm_name, A.expiry, A.strike, B.description " & _
"HAVING (((A.desk_id)='MGMT') AND ((A.lhu_id)='MGMTT') AND ((Sum(A.position))<>0));"

[austenr]

Bob is right. You should move this post to the Access forum. True it is part Excel but the majority of the problem seems to be with Access functions. I will let you decide what to do. As far as your reference problem goes, I had to reverse engineer SQL Server to Access once and it can be frustrating. I had to get a lot of help, mostly from this board to solve my problems. Good luck. I know what you are going through.

[ncarty97]

thanks for the help. Like I said, the query seems to work fine in Access. I think it may be a problem with the join function, but I'm not sure. I've posted in the Access forum as well.

[austenr]

Hmm. Maybe. Although, Access is reading the query from excel so that might not be it. Im sure someone can help you over there. Good Luck.

[Bob Phillips]

If you are using the Jet engine in the connection string, it is Access doing the query anyway, so if it works in Access it should work just as well automated from Excel.

[ncarty97]

That's what I thought!

[rory]

What is the actual code you have - what you posted won't compile (double quotes before HAVING on the last line of the strSQL build) so it can't be exactly what you have. There also shouldn't be parentheses round the strSQL argument of the rs.Open method though I don't believe that is the problem.
I suspect the references issue may be the root cause - which reference are you having issues with?

[ncarty97]

Not sure how the double quotes ended up there as they are not in the original code. Here is the code:

strSQL = ""
strSQL = "SELECT A.lhu_id, A.ticker, A.book_currency, A.instrument_type, " strSQL = strSQL & "A.mkt_price, A.under_ticker, A.firm_name, A.expiry, A.strike, B.description, Sum(A.position) AS SumOfposition "
strSQL = strSQL & "FROM position_mapping AS B RIGHT JOIN imo_positions AS A ON B.ticker = A.ticker "
strSQL = strSQL & "GROUP BY A.desk_id, A.lhu_id, A.ticker, A.book_currency, A.instrument_type, A.mkt_price, A.under_ticker, "
strSQL = strSQL & "A.firm_name, A.expiry, A.strike, B.description "
strSQL = strSQL & "HAVING (((A.desk_id)=""MGMT"") AND ((A.lhu_id)=""MGMTT"") AND ((Sum(A.position))<>0));"

When it compiles, the strSQL value is:

SELECT A.desk_id, A.lhu_id, A.instrument_type, A.ticker, A.under_ticker, A.expiry, A.firm_name, A.book_currency, A.mkt_price, A.option_type, A.strike, B.description, Sum(A.position) AS SumOfPosition FROM position_mapping AS B RIGHT JOIN imo_positions AS A ON B.ticker = A.ticker GROUP BY A.desk_id, A.lhu_id, A.instrument_type, A.ticker, A.under_ticker, A.expiry, A.firm_name, A.book_currency, A.mkt_price, A.option_type, A.strike, B.description HAVING (A.desk_id="MGMT") and (Sum(A.Position)<>0) And (lhu_id="MGBQI") Order by A.ticker;


The reference problem I am having is with the Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 Library, but that reference isn't what I am using for this project.

[Bob Phillips]

So set the reference to the one you are using, or use late binding.

[rory]

What happens if you uncheck the ADO Multidimensional reference and compile the code and save the workbook?

[stanl]

Just my .02 - but you have both a Connection and Command TimeOut which may be ignored by a Recordset Object. Why not set command timeout to 0 and use the Connectiuon.Execute to return a recordset. I think the Connection Timeout needs to be set as part of the connect string.

Stan