Limitations of ADO?

[JapanFreak]

Hi there,

actually I have already asked the following question in an Excel forum (www.excelforum.com) before, but I only got the advice to try to ask this question in this MS Access-related forum. Thus, here it is: I have a problem with regards to retrieving data from an MS Access 2000 database by using ADO in Excel VBA.

If I try my Excel code with very simple SQL codes, the code actually works and delivers the desired results on my Excel sheet starting from Range A1. Hence I assume that there is no problem with my ADO request as such.

However, I have problems (run-time error ‘-2147467259 (80004005)’ because of problems with the open method for object recordset) when I use (not much) more complex SQL requests with sub-queries. I developed those queries in MS Access and they work properly there, i.e. I assume that there is no problem with my SQL code as such either.

Are there any known limitations with regards to the complexity of SQL statements via ADO? At least my google search does not deliver any results... Are there any other mistakes in my code?

This is my code with a very simple SQL-request, that actually works in both Access and Excel:

Sub ADORequest()
    Dim iCounter As Integer
    Dim PathAccessFile As String
    Dim ReqDate As Date
    Dim strSQL As String
    Dim TargetRange As Range
    Dim CN As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Worksheets("Data").Cells.ClearContents

    PathAccessFile = ThisWorkbook.path & "\TEST.mdb"

    strSQL = "SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Qty]) AS TotQty" & _
        " FROM tblTransactions" & _
        " WHERE [tblTransactions].[Date] <= #3/10/2007#" & _
        " GROUP BY [tblTransactions].[Ticker]" & _
        " HAVING Sum(tblTransactions.Qty) > 0" & _
        " ORDER BY [tblTransactions].[Ticker]"

    Set TargetRange = Range("a1")
    
    Set CN = New ADODB.Connection
        
    With CN
        .cursorlocation = adUseClient
        .Mode = adModeShareDenyNone
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & PathAccessFile
        .Open
    End With

    Set rs = New ADODB.Recordset
    
    With rs
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open Source:=strSQL, _
            ActiveConnection:=CN, _
            CursorType:=adOpenStatic, _
            LockType:=adLockReadOnly, _
            Options:=adCmdText
    
        For iCounter = 0 To rs.Fields.Count - 1
            TargetRange.Offset(0, iCounter).Value = rs.Fields(iCounter).Name
        Next iCounter
        
        TargetRange.Offset(1, 0).CopyFromRecordset rs
    End With
    
    rs.Close
    CN.Close
    
    Set CN = Nothing
    Set rs = Nothing
    Set TargetRange = Nothing
End Sub

However, the same VBA code with an only a bit more complex SQL request with a sub-query only produces an error, i.e. the query does work in Access but the Excel request via ADO fails:

…
strSQL = "SELECT T.Ticker, S.Name, X.Close" & _
" FROM tblTransactions AS T, tblTradedSecurities AS S," & _
" [SELECT Q.qTicker, Q.qCl AS Close" & _
" FROM tblQuotes AS Q" & _
" WHERE Q.qDate=#3/9/07#]. AS X" & _
" WHERE (((T.Ticker) = [S].[Ticker]) And ((S.Ticker) = [Q].[qTicker]))" & _
" GROUP BY T.Ticker, S.Name, X.Close"
…

What is the reason? Are there any limitations of ADO objects which I do not take into account?

I'd be happy for any comments / help regarding this issue...

Best regards,
JapanFreak

 

[RoyVidar]

I don't know, but some thoughts.

I'd first remove any reserved words (see http://support.microsoft.com/kb/321266/EN-US/ -> "close"), and see if that was enough.

If not, I think I'd try replacing the [brackets] around the subquery with (parens), perhaps also removing the dot (.), as I think that's more Access dialect than Jet. See - what you're using from Excel, isn't an Access database, but a Jet database, through the OLE DB provider. The Jet database, is the default database that Access uses.

Here's a resent discussion from a query NG http://groups.google.com/group/microsoft.public.access.queries/browse_frm/thread/daee580dcaaef551/
perhaps it can shed some light too?

Anyway, your FROM clause, uses the "old" syntax, perhaps you could/should experiment with the newer syntax, using INNER JOIN, LEFT/RIGHT JOIN?

But again, I don't know, it's just some thoughts.

 

[Pat Hartman]

It looks to me like there's an extraneous "." You also have two objects named close. And finally, when working with Jet, use joins rather than subqueries since Jet optimizes them better.

 

[JapanFreak]

Problem solved - 2 remaining questions

Hi,

thank you very much for your replies. Actually it was a combination of various things. This SQL request works:

SELECT T.Ticker, S.Name, X.qCl
FROM tblTransactions AS T, tblTradedSecurities AS S
  (SELECT Q.qTicker, Q.qCl
   FROM tblQuotes AS Q
   WHERE Q.qDate=#3/9/07#) AS X
WHERE T.Ticker = S.Ticker And S.Ticker = Q.qTicker
GROUP BY T.Ticker, S.Name, X.qCl

However, two questions remain:
- Where can I find further information about SQL queries for a Jet database? Obviously there are differences between such and those decribed in standard MS Access literature...
- I'm not really sure how to replace the sub-query with a JOIN in this case...

Thank you very much again & best regards,

JapanFreak

 

[RoyVidar]

I'm hopeless with SQL, so I won't even try

There are some articles, here is one http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx, which also links to two other in the same series (at the bottom of the article)

When Jet 4.0 was introduced around mid 1999, I think, there were lot of new features. Unfortunately, several of them are not supported through the Access interface, but only through ADO/OLE DB, which did not gain the popularity Microsoft wished, so most Access developers use and recommend DAO for all interaction with Jet (and also ODBC linked tables).

Here's a Microsoft Excel newsgroup, where you can probably find a bit more ADO/Excel/Jet stuff http://groups.google.com/group/microsoft.public.excel.programming/topics

 

[wazz]

http://www.pearsoned.co.uk/Bookshop/detail.asp?item=100000000039056

you'll be able to skip the first few chapters of this book i'm sure. (but, i think all of the samples in the book are based on a db that is created at the start so you might want to check out the beginning too). the rest has very good info on joins and many other things. i find it to be very clearly written. it is strictly access/jet but includes occasional comments in the borders on how jet differs with with other versions of SQL.

if you go to the link above, scroll down to see the table of contents.