BOF/EOF Error using GetRows() with 1 record

[VBAhack]

Using ADO within Excel (2013) VBA to extract records from an Access file. For some reason I get a BOF/EOF error when using GetRows() if only one record (i.e. RecordCount = 1) is in the recordset. I've dug around and found nothing about a limitation of GetRows() in case of 1 record only. I can certainly test for Recordcount=1 and copy the field values in a loop into the array rather than use GetRows() but that seems like a goofy workaround. Am I doing someone wrong?

[digitalShaman]

Am I doing someone wrong?

hard to say without seeing your code. are you doing some loop around the getrows? then i'd say you loop once too often because i tried getrows one a single record and this works nicely:

Sub a()
    Dim cn As New Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database1.accdb;Persist Security Info=False;"
    Dim rs As Recordset
    Set rs = cn.Execute("SELECT TOP 1 * FROM tbl1")
    Dim vFieldValues() As Variant
    vFieldValues = rs.GetRows
    cn.Close
End Sub

while this fails:

Sub a()
    Dim cn As New Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database1.accdb;Persist Security Info=False;"
    Dim rs As Recordset
    Set rs = cn.Execute("SELECT TOP 1 * FROM tbl1")
    Dim vFieldValues() As Variant
    vFieldValues = rs.GetRows
    vFieldValues = rs.GetRows
    cn.Close
End Sub

[VBAhack]

Thanks for your reply. I tried a test and got the same result as you, which means I must somehow be incrementing the recordset pointer to EOF. I now know what to look for.

Sub testADO()
    Dim rsString As String
    Dim num As Long
    Dim v As Variant
    
    rsString = "SELECT TOP 1 * FROM " & strTable
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = strPROVIDER
    cn.Properties("Data Source") = [rFileDB].Value
    cn.Open
    rs.Open rsString, cn, adOpenStatic, adLockOptimistic, adCmdText
    num = rs.RecordCount
    v = rs.GetRows
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Debug.Print "num=" & num, "LB=" & LBound(v, 2), "UB=" & UBound(v, 2)
    'num=1         LB=0          UB=0'
End Sub