none
ADODB returns empty recordset (should not be empty) RRS feed

  • Question

  • I have an Access database. It has several linked tables and a bunch of views. I am using ADODB objects in Excel to grab the data from the Access database. I run three queries, all are non-empty when they run in Access. When I run them through VBA in Excel, two return the correct data while the third returns an empty dataset (initially set to EOF). Any idea why that might occur? My SQL statement is extremely straightforward:

    SELECT *

    FROM MyView

    That's it. It is not supposed to be a complicated query. It is just supposed to grab the data and drop it into Excel. I cannot figure out why the recordset is empty. I tried removing the other two queries, I've tried reordering them, and nothing works.

    Monday, October 24, 2016 9:44 PM

Answers

All replies

  • Hi,

    Could you please share your VBA code with us to have a test?

    I could reach all the data from the query using code below.

    Sub qryAccess()
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
     
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
     
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Demo.mdb;"
       
    objRecordset.Open "Select * FROM [Sheet1]", objConnection, adOpenStatic, adLockOptimistic, adCmdText
     
    Do Until objRecordset.EOF
      Debug.Print objRecordset.Fields("Name").Value
      objRecordset.MoveNext
    Loop
     
    End Sub


    Tuesday, October 25, 2016 2:59 AM
    Moderator
  • Sub GetData()
        Dim DBAccess As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim rowNum As Integer
        Set DBAccess = New ADODB.Connection
        
        DBAccess.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Path\FileName.mdb;Persist Security Info=False;"
        DBAccess.Open
        
        Set rs = DBAccess.Execute("SELECT * FROM [ACTIVE NOW]")
        rowNum = 1
        While Not rs.EOF
            With ActiveWorkbook.Worksheets("Sheet1")
                .Cells(rowNum, 1).Value = rs("LastOfDSINBQ")
                .Cells(rowNum, 2).Value = rs("CountOfLastOfDSINBQ")
                rowNum = rowNum + 1
                rs.MoveNext
            End With
        Wend
        rs.Close
        DBAccess.Close
        Set rs = Nothing
        Set DBAccess = Nothing
    End Sub
    

    This code works great, but if I switch the code to a different view (which I know has exactly two rows and two columns), it fails to return any data.
    Tuesday, October 25, 2016 5:47 PM
  • Hi,

    The code works fine with my files too. If you ensure the query would not be empty, what do you mean "switch the code to a different view"? Do you get any error when it failed to get data?

     

    If the database is opened in Access, when the table or a query related to the table is opened, the macro would cause Excel application crash. So when running the macro, we are unable to open the related table or query in Access.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, October 26, 2016 6:45 AM
    Moderator
  • The code above is running with the view [ACTIVE NOW], which returns 7-10 rows of data. If I change the view in the code to [SYS COUNTS] (which returns 2 rows when I view the query in Access), it generates a Recordset with a RowCount of -1, the Recordset starts with both BOF and EOF set to TRUE (so it did not return any results).

    There is no error message when I try to get data from the [SYS COUNTS] query. It just returns no data when I try to pull the data through VBA, but it does return data when I open the query directly in Access.

    The only things that I can think that might affect it is that, the database itself has no password, but it does have a linked table, which is on a remote server. When I first open the Access database, it asks for the username and password of the remote server. Is there a way to set that in the ADODB.Connection properties somehow?


    EDIT: I am not positive, but I believe the [ACTIVE NOW] query is pulling data from the remote server and working fine, which further confuses me.
    Wednesday, October 26, 2016 3:31 PM
  • Hi,

    Do all the three tables are linked tables? Are they in the same database?

    Do you try to query other normal tables in this database by using ADO and does it return empty in the VBA?

    I suggest you check the sheet for the linked table.

    To use ADO connection for a protected database, you could see the following KB:

    How To Link and Refresh Linked Jet Tables Using ADOX

    How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database

    If the database is in the remote server, I suggest modify the connection string: Provider=MS Remote, you visit Microsoft OLE DB Remoting Provider (ADO Service Provider)

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 28, 2016 12:05 PM
    Moderator