Allowed - EOF value is always true, even if there is a record returned from VBA SQL

Question

I query the access table using VBA and write the result of the query in excel.

EOF is always true, but BOF is False - even if the number of entries is 1 or 14 or 100. What could possibly be wrong? I see that the number of entries is greater than zero. get string has data in it. In this regard, there is no data recorded in the addressee sheet, with the exception of the headers. The headings are fine.

The list of attempts, but the result was the same:

• Added Move last and Move first command
• Tried all possible combinations of cursor location, cursor type, lock type
• Tried with run command
• Tried with another MS access table
• Well-developed methods for early and late binding

Below is my query and link below, as my record set looks after the 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

During debugging, this is what my record set looks like:

Answer 1

Based on this answer to a similar question , calling getStringa Recordset object has the side effect of moving a record set to EOF.

You do not call getStringanywhere in your code, but you have added a watch to rs.getString, which appears as the last entry in the watch window. If you have a clock on rs.getString, and you have a breakpoint in the code where it is open rs, then this breakpoint will cause the recordset to transition to EOF.

, , (, EOF), EOF, .

, rs.getString. , , "" . , , , .

getString EOF

Answer 2

, :

Set rs = Open ........

Dim ws as WorkSheet, rowNum as long
Set ws = Worksheets("YourSheetName")
rowNum = 0 'set this to how many rows to skip at the top

With rs
    .MoveLast
    .MoveFirst 
    Do While Not .EOF
        rowNum = rowNum + 1
        'I assume you know the names of your fields?
        'If so refer to them by name, not in that loop
        ws.cells(rowNum,1) = !yourfieldname1
        ws.cells(rownum,2) = !yourfieldname2   
        ws.cells(rownum,3) = !yourfieldname3   'etc....
    Loop
    .Close
End With
set rs = Nothing
set ws = Nothing

.