EOF value is always true even if there is record returned from VBA SQL

Question

I am querying my access table using VBA and writing the query result in excel.

EOF is always true but BOF is False - even if the record count is 1 or 14 or 100. What possibly would be wrong? I can see the record count more than zero. get string value has data in it. Due to this there is no data written in the destination sheet except for Headers. The headers are coming in fine.

List of things tried but result was still same:

1. Added Move last and Move first command
2. Tried all possible combinations of Cursor location, cursor type, lock type
3. Tried with execute command
4. Tried with different MS access table
5. Tried early and late binding techniques

Below is my query and link below is my how my record set looks after SQL open statement.

    Const MyConn = "Access DB location"
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    With con
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .Open MyConn
    End With

    QuerySql = "SELECT * FROM Store_Location"

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open QuerySql, con, adOpenStatic, adLockReadOnly, adCmdUnknown
    rs.MoveLast
    rs.MoveFirst


    i = 0  
    For i = 0 To rs.Fields.Count - 1
        Sheets("Search_Temp").Cells(1, i + 1) = rs.Fields(i).Name
    Next i

    Range("A2").CopyFromRecordset rs  


    rs.Close
    Set rs = Nothing
    con.Close
    Set con = Nothing

While debugging this is what my record set looks like:

Answer 1

Building on this answer to a similar question, calling getString on a Recordset object has a side-effect of moving the recordset to EOF.

You don't call getString anywhere in your code but you've added a watch on rs.getString which is visible as the last entry in the Watches window. If you have a watch on rs.getString and you have a breakpoint in the code where rs is open then that breakpoint will cause the recordset to move to EOF.

Depending on where the breakpoint occurs, this might not cause any issues (e.g. if the recordset was already at EOF) but, in this case, it is moving the recordset to EOF before you have copied the data from the recordset.

To solve this issue, remove the watch on rs.getString. It's probably a bad idea in general to have items in the Watches window cause side effects. You could also avoid the issue by not having any breakpoints where the recordset is open but removing the watch entirely is more robust.

The issue of getString moving the recordset to EOF isn't mentioned in the ADO documentation but it's easy to reproduce this effect.

It's uncommon for someone to include the entire list of watches they had set in their question but I'm not sure this question was answerable without that information