0

I'm developing an Excel VBA application using ADO. I'm trying to open a recordset, but the open method fails if my table has more than 65536 lines. I know this number is the old Excel line limit, but I'm using the 2016 version and the correct connection strings. Perhaps it's some library referenced in my project, but I can't find out which one.

I would appreciate very much if I could get some help with this.

The error: Runtime error '-2147217865 (80040e37)': The Microsoft Jet database engine could not find the object 'My sheet$A8:AD70000'. Make sure the object exists and that you spell its name and the path name correctly.

My code (I had to switch the worksheet name so I don't reveal any sensitive data):

Sub MySub()

Dim conn    As ADODB.Connection
Dim rs      As ADODB.Recordset
Dim str     As String

Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection

str = "SELECT * FROM [My sheet$A8:AD70000];"

'Opening connection with the workbook
conn.ConnectionTimeout = 90
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & Application.ThisWorkbook.FullName & ";" & _
                        "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1"";"
conn.Open

rs.Open str, conn, adOpenForwardOnly, adLockReadOnly, adCmdText

rs.Close
conn.Close

End Sub

If I change "My sheet$A8:AD70000" to "My sheet$A8:AD60000", this code Works.

The libraries I'm using:

Visual Basic For Applications

Microsoft Excel 16.0 Object Library

Microsoft Forms 2.0 Object Library

Microsoft Office 16.0 Object Library

Microsoft ActiveX Data Objects 6.1 Library

OLE Automation

Thank you very much for your attention.

gscofano
  • 83
  • 10
  • Why not just calculate the last row and stop there? – braX Oct 24 '17 at 19:21
  • Thank you for you comment. I have to export the whole table, I can't lose any data. I'll have to copy this recordset somewhere later, but my concern right now is to open it in the first place. – gscofano Oct 24 '17 at 19:27
  • Is your source file saved in "compatibility mode"? What format is it? – Tim Williams Oct 24 '17 at 19:50
  • It is a .xlsm file. I'll check if it is in compatibility mode as soon as I get it in the morning, but I'm pretty sure I didn't set this up. Thanks for your idea, Tim. – gscofano Oct 25 '17 at 04:05

0 Answers0