Getting data into Excel from Access using ADO

[ncarty97]

Howdy!

I put this in the excel forum, but was told I might have better luck here.

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!