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

Question

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'.

Excel had a 65K row limit until Office 2007 I believe. Are you using an older version? — wavery, May 03 '19 at 18:33
What is the error you receive? Please wrap your subroutine in proper [error handling](https://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling) and report the runtime exception messages. — Parfait, May 03 '19 at 21:00
Solved: Pass the sheet object -- strSQL = "SELECT * FROM [Sheet1$]" — Geosmile, May 04 '19 at 15:48

Van Ng · Accepted Answer · 2019-05-03T23:00:35.733

1

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.

edited May 03 '19 at 23:00

answered May 03 '19 at 20:15

Van Ng

773
1
7
17

There no such `16.0` OLEDB driver. – Parfait May 03 '19 at 20:58
https://stackoverflow.com/questions/40360932/microsoft-ace-oledb-16-0-provider-is-not-registered-on-the-local-machine-sys and this? – Van Ng May 03 '19 at 21:00
Where is `Microsoft.ACE.OLEDB.16.0` used? Accepted answer does not show it. The OLEDB version number does not align with Microsoft Office year number. – Parfait May 03 '19 at 21:03
Calling 12.0 version dues to calling object library that pulls only 65536 rows, as I think. Calling 16.0 version library should get over this limitation. 16 and 2016 just a accidental coincidence – Van Ng May 03 '19 at 21:07
1

I stand corrected. Until today I only knew of `Microsoft.OLEDB.Jet.4.0` and `Microsoft.OLEDB.ACE.12.0` OLEDB drivers. I adjusted one of my code to use 16.0 version and it worked! What's remarkable is that even the [2016 redistributable](https://www.microsoft.com/en-us/download/details.aspx?id=54920) site mentions only 12.0 under *Install Instructions*. No were does MSDN docs mention 16.0. Let me read into this more. – Parfait May 03 '19 at 21:32
Thanks all, I did try the switch to Microsoft.ACE.OLEDB.16.0 and it still limits at 65536 rows - could it be a record set limit? – Geosmile May 03 '19 at 21:45
Do you get same error message? And what type of file are you opening? I assume it to be xlsx, but let it make clear – Van Ng May 03 '19 at 22:02
@Parfait I've just stacked with these problems while connecting to xlsx files through Openrowset method. And same - there were only code examples in the net – Van Ng May 03 '19 at 22:04
Working on error handling wrapper, the error returned is Run-time error '-2147217865 (80040e37)': The Microsoft Access database engine could not find the object 'Sheet1$A1:AI65437'. Make sure the object exists and that you spell its name and path name correctly... The file type is xlsm – Geosmile May 03 '19 at 22:29
1

Answer was near https://stackoverflow.com/questions/24472183/excel-as-database-query-more-than-65536-rows interesting. You cannot mention rows, or you'll get error. You can mention columns only. Seems to be driver bug. – Van Ng May 03 '19 at 22:41
1

strSQL = "SELECT * FROM [Sheet1] WHERE GROUP = 'HIX'" something like that should be – Van Ng May 03 '19 at 22:43
1

Success!! returned all 146203 rows! strSQL = "SELECT * FROM [Sheet1$]" Thanks - appreciate your efforts! @VanNg,@Parfait – Geosmile May 03 '19 at 22:55

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

1 Answers1

Linked