Issue with ODBC Object connection – Open limitation to 65k rows

New to programming, SQL and VBA. I frequently work with decent size data tables and thought it would be helpful to add SQL query execution capability to apply to an existing excel table. Research led me to ADODB connections and found a great base snippet to work from here: https://blog.learningtree.com/excel-as-a-database-how-to-query-economic-data-with-sql/

I seem to running into limits though on how many rows are accessible before the next line of code runs. In my SQL statement source I can return 65k rows, any more in defining the source table size, and I get an Object does not exist error. Can you run ADODB recordset.Open asynchronously to ensure complete return of the object? – Any help would be very much appreciated. Thanks!

tried to insert a WAIT inline: rs.Open strSQL, Application.Wait (Now + TimeValue("0:00:30")), cn but still errors out. See code below

Sub ExcelTbl_SQL()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
                & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    strSQL = "SELECT * FROM [Sheet1$A1:AI146103] WHERE GROUP = 'HIX'"

    rs.Open strSQL, cn

    Dim ws As Worksheet

    Set ws = Application.Sheets.Add    
    ws.Range("A1").CopyFromRecordset rs

    rs.Close    
    cn.Close
    'Debug.Print rs.GetString    
End Sub

These are the results I am getting:

Works: strSQL = "SELECT * FROM [Sheet1$A1:AI65000] WHERE GROUP = 'HIX'"
Error: strSQL = "SELECT * FROM [Sheet1$A1:AI65437] WHERE GROUP = 'HIX'"

Run-time error ‘-2147217865 (80040e37)’: The Microsoft Access database engine could not find the object ‘Sheet1$A1:AI65437’.

Answer

I think this is because you are calling old version library through this part of connection string:

Provider=Microsoft.ACE.OLEDB.12.0

You should try

Provider=Microsoft.ACE.OLEDB.16.0

Upd: Answer was here Excel as database – query more than 65536 rows? interesting. You cannot mention rows, or you’ll get error.

Advertisement

Answer