What's the most efficient way for accessing a single record in an ADO recordset?

Question

I want to access individual records in a classic ADO recordset without enumerating over the entire recordset using .MoveNext. I'm aware of using AbsolutePosition as well as .Filter =. What's the best way?

More Details: I'm likely going to be accessing the recordset several times pulling out individual records that match a list of records in a particular field. For example, I have a recordset with records that have field values ranging from 1 to 100, I might have a separate array containing just {34, 64, 72}, and I want to do something to only the records in the recordset whose IDs are contained in the array.

Answer 1

I ended up rewriting my answer due to new information, so:

My suggestion is to set the Filter property to what you want, then enumerate through the resulting subset and assign the Bookmark value of each record in the subset to a variable that you can easily match up with the IDs (so you might want to put them in an array in the order that their IDs are in the ID array you mention).

Answer 2

If you are using server-side cursors, then the best method depends on the underlying OLE DB provider that you are using. It is quite possible that each access of the record could result in another trip to the server to read the data.

If you can use a client-side cursor, then I suspect that AbsolutePosition will be the best method to move to each record repeatedly. I believe that using a filter with a client-side cursor would require that it spin through each record matching the filter condition.

Answer 3

Use the Filter function on the Recordset object.

rs.Filter = "ID = '" & strID & "'"

Answer 4

I'm using this function all the time

Public Function InitIndexCollection( _
            rs As Recordset, _
            sFld As String, _
            Optional sFld2 As String, _
            Optional sFld3 As String, _
            Optional ByVal HasDuplicates As Boolean) As Collection
    Const FUNC_NAME     As String = "InitIndexCollection"
    Dim oFld            As ADODB.Field
    Dim oFld2           As ADODB.Field
    Dim oFld3           As ADODB.Field

    On Error GoTo EH
    Set InitIndexCollection = New Collection
    If Not IsRecordsetEmpty(rs) Then
        Set oFld = rs.Fields(sFld)
        If LenB(sFld2) <> 0 Then
            Set oFld2 = rs.Fields(sFld2)
        End If
        If LenB(sFld3) <> 0 Then
            Set oFld3 = rs.Fields(sFld3)
        End If
        If HasDuplicates Then
            On Error Resume Next
        End If
        With rs
            If oFld2 Is Nothing Then
                .MoveFirst
                Do While Not .EOF
                    InitIndexCollection.Add .Bookmark, C_Str(oFld.Value)
                    .MoveNext
                Loop
            ElseIf oFld3 Is Nothing Then
                .MoveFirst
                Do While Not .EOF
                    InitIndexCollection.Add .Bookmark, C_Str(oFld.Value) & "#" & C_Str(oFld2.Value)
                    .MoveNext
                Loop
            Else
                .MoveFirst
                Do While Not .EOF
                    InitIndexCollection.Add .Bookmark, C_Str(oFld.Value) & "#" & C_Str(oFld2.Value) & "#" & C_Str(oFld3.Value)
                    .MoveNext
                Loop
            End If
        End With
    End If
    Exit Function
EH:
    RaiseError FUNC_NAME
    Resume Next
End Function