VBA/Excel/Access/Word/Access/Recordset Bookmark

declares a Variant variable named myBookmark and then assigns to it a bookmark representing the current record in an ADO recordset:

 
Sub main()
    Dim myDatabase As dao.Database
    Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb")
    
    Dim myRecordset As dao.Recordset
    Set myRecordset = myDatabase.OpenRecordset(Name:="SELECT * FROM Customers WHERE Country="Germany"", Type:=dbOpenDynaset)
    Dim myBookmark As Variant
    myBookmark = myRecordset.Bookmark
End Sub

Does it support bookmark

 
Sub BookmarkSupport()
   Dim rst As ADODB.Recordset
   Dim varMyBkmrk() As Variant
   Dim strConn As String
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
   Set rst = New ADODB.Recordset
   rst.Open "Customers", strConn, adOpenKeyset
   If Not rst.Supports(adBookmark) Then
      MsgBox "This recordset does not support bookmarks!"
      Exit Sub
   End If
   rst.Close
   Set rst = Nothing
End Sub

Marking Records with a Bookmark

 
Sub TestBookmark()
   Dim rst As ADODB.Recordset
   Dim varMyBkmrk As Variant
   Set rst = New ADODB.Recordset
   rst.Open "Employees", CurrentProject.Connection, adOpenKeyset
   If Not rst.Supports(adBookmark) Then
      MsgBox "This recordset does not support bookmarks!"
      Exit Sub
   End If
   varMyBkmrk = rst.Bookmark
   Debug.Print rst.Fields(1).Value
   rst.AbsolutePosition = 7
   Debug.Print rst.Fields(1).Value
   rst.Bookmark = varMyBkmrk
   Debug.Print rst.Fields(1).Value
   rst.Close
   Set rst = Nothing
End Sub

Save Bookmark to an array

 
Sub BookmarkArray()
   Dim rst As ADODB.Recordset
   Dim varMyBkmrk() As Variant
   Dim strConn As String
   Dim i As Integer
   Dim strCountry As String
   Dim strCity As String
   i = 0
   strCountry = "France"
   strCity = "Paris"
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
   Set rst = New ADODB.Recordset
   rst.Open "Customers", strConn, adOpenKeyset
   If Not rst.Supports(adBookmark) Then
      MsgBox "This recordset does not support bookmarks!"
      Exit Sub
   End If
   Do While Not rst.EOF
      If rst.Fields("Country") = strCountry And _
         rst.Fields("City") = strCity Then
         ReDim Preserve varMyBkmrk(i)
         varMyBkmrk(i) = rst.Bookmark
         i = i + 1
      End If
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End Sub

Using Bookmarks to Filter Records

 
Sub Filter_WithBookmark()
   Dim rst As ADODB.Recordset
   Dim varMyBkmrk() As Variant
   Dim strConn As String
   Dim i As Integer
   Dim strCountry As String
   Dim strCity As String
   i = 0
   strCountry = "France"
   strCity = "Paris"
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
   Set rst = New ADODB.Recordset
   rst.Open "Customers", strConn, adOpenKeyset
   If Not rst.Supports(adBookmark) Then
      MsgBox "This recordset does not support bookmarks!"
      Exit Sub
   End If
   Do While Not rst.EOF
      If rst.Fields("Country") = strCountry And _
         rst.Fields("City") = strCity Then
         ReDim Preserve varMyBkmrk(i)
         varMyBkmrk(i) = rst.Bookmark
         i = i + 1
      End If
      rst.MoveNext
   Loop
   rst.Filter = varMyBkmrk()
   rst.MoveFirst
   Do While Not rst.EOF
      Debug.Print rst("CustomerId") & _
         " - " & rst("CompanyName")
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End Sub