none
Error opening recordset in VBA RRS feed

  • Question

  • 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:

    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.

    Wednesday, October 25, 2017 12:10 PM

All replies