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:

   <source lang="vb">

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

</source>
   
  

Does it support bookmark

   <source lang="vb">

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

</source>
   
  

Marking Records with a Bookmark

   <source lang="vb">

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

</source>
   
  

Save Bookmark to an array

   <source lang="vb">

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

</source>
   
  

Using Bookmarks to Filter Records

   <source lang="vb">

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

</source>