ADO - ADO Recordset Bookmark Property

The Bookmark property in ADO (ActiveX Data Objects) is used to identify a particular record in a Recordset so that an application can return to that record later without having to navigate through all the records again. A bookmark acts like a unique position marker within the current Recordset. This is especially useful when an application needs to temporarily move to another record, perform an operation, and then return to the original record.

1. What is a Bookmark?

A bookmark is a value that represents the current position of a record in an ADO Recordset. When the Recordset supports bookmarks, the application can store the current record's bookmark in a variable.

For example:

Dim savedBookmark As Variant

savedBookmark = rs.Bookmark

Here, rs.Bookmark returns the bookmark of the current record, and that value is stored in savedBookmark.

The application can later use this value to return to the same record:

rs.Bookmark = savedBookmark

This moves the current position back to the record represented by the saved bookmark.

2. Why is the Bookmark Property Used?

Normally, records can be accessed sequentially using methods such as MoveFirst, MoveNext, MovePrevious, and MoveLast. However, repeatedly navigating through a large Recordset can be inefficient.

Suppose a Recordset contains 10,000 records and the application is currently positioned at record 8,000. If the application moves to another record and later needs to return to record 8,000, navigating from the beginning would be unnecessary.

A bookmark allows the application to save the current position:

savedBookmark = rs.Bookmark

The application can then move elsewhere:

rs.MoveFirst

After completing another operation, it can return directly:

rs.Bookmark = savedBookmark

This makes bookmark-based navigation convenient and easier to manage.

3. Reading the Bookmark

The Bookmark property can be read to obtain the bookmark associated with the current record.

Dim currentBookmark As Variant

currentBookmark = rs.Bookmark

The value returned by the Bookmark property should generally be stored in a Variant, because the actual bookmark representation depends on the ADO provider.

A bookmark should not normally be treated as a meaningful numeric record number. It is an identifier used by ADO to locate a particular record within the Recordset.

4. Setting the Bookmark

The Bookmark property can also be assigned a previously saved bookmark.

rs.Bookmark = currentBookmark

After this statement executes successfully, the current record becomes the record represented by the bookmark.

For example:

Dim savedBookmark As Variant

savedBookmark = rs.Bookmark

rs.MoveLast

rs.Bookmark = savedBookmark

The Recordset first moves to the last record and then returns to the previously saved record.

5. Basic Example

Consider a Recordset containing employee information:

Dim rs As ADODB.Recordset
Dim savedBookmark As Variant

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName FROM Employees", connection

savedBookmark = rs.Bookmark

rs.MoveLast

rs.Bookmark = savedBookmark

In this example, the application saves the position of the current record, moves to the last record, and then restores the original position.

6. Checking Whether Bookmarks Are Supported

Not every ADO Recordset necessarily supports bookmarks. The Supports method can be used to determine whether a particular Recordset supports the adBookmark capability.

If rs.Supports(adBookmark) Then
    savedBookmark = rs.Bookmark
End If

This is important because bookmark support can depend on the cursor type, provider, and underlying data source.

A safer implementation is therefore:

If rs.Supports(adBookmark) Then
    savedBookmark = rs.Bookmark
    ' Perform other operations
    rs.Bookmark = savedBookmark
End If

This prevents an application from assuming that every Recordset supports bookmarks.

7. Bookmark and Recordset Navigation

The Bookmark property is different from ordinary navigation methods.

For example:

rs.MoveFirst
rs.MoveNext
rs.MovePrevious
rs.MoveLast

These methods move relative to the current position or to the beginning/end of the Recordset.

The Bookmark property, on the other hand, allows the application to return to a previously saved position.

Consider:

savedBookmark = rs.Bookmark

rs.MoveNext
rs.MoveNext
rs.MoveNext

rs.Bookmark = savedBookmark

The application moves forward three records and then returns to the record whose bookmark was saved.

8. Bookmark Is Not the Same as a Primary Key

A common misunderstanding is that a bookmark is the same as a database primary key.

They are different concepts.

A primary key is a value stored in the database that uniquely identifies a row. For example:

EmployeeID = 105

A bookmark is an ADO-specific value used to identify a record's position in a Recordset.

For example:

savedBookmark = rs.Bookmark

The bookmark does not replace the primary key and should not generally be stored as a database identifier.

If an application needs to locate a database record reliably after reopening the Recordset, it is usually better to use a primary key and an appropriate query or filtering mechanism rather than relying on a previously saved bookmark.

9. Bookmark and Current Record

The Bookmark property is closely associated with the current record.

Suppose the Recordset contains:

EmployeeID    EmployeeName
101           Arun
102           Ravi
103           Meena
104           Priya

If the current record is:

103    Meena

the application can save its bookmark:

savedBookmark = rs.Bookmark

After moving to another record, the application can restore the saved position:

rs.Bookmark = savedBookmark

The current record becomes the record that was originally selected.

10. Using Bookmark with User Interface Applications

Bookmarks are particularly useful in applications that display database records in forms or grids.

For example, imagine an employee-management application where the user selects an employee. The application can save the selected record's bookmark before performing another operation.

Dim employeeBookmark As Variant

If rs.Supports(adBookmark) Then
    employeeBookmark = rs.Bookmark
End If

The program can then perform other Recordset operations. Once those operations are complete, it can restore the selected employee:

If rs.Supports(adBookmark) Then
    rs.Bookmark = employeeBookmark
End If

This allows the interface to preserve the user's position.

11. Bookmark and Sorting or Filtering

Care must be taken when applying operations that significantly change the Recordset's contents or ordering.

For example:

savedBookmark = rs.Bookmark

Then the application applies a filter:

rs.Filter = "Department = 'Sales'"

The original bookmark may not always be usable in the resulting Recordset if the corresponding record is no longer included.

Similarly, changes to the Recordset or provider behavior can affect whether a previously obtained bookmark remains valid.

Therefore, bookmarks should generally be treated as temporary navigation values rather than permanent identifiers.

12. Bookmark and Recordset Cursor Types

Bookmark support is influenced by the type of cursor being used.

For example, some cursor configurations provide better navigation capabilities than others. A Recordset intended for advanced navigation should be configured with a cursor that supports the required functionality.

Before relying on bookmarks, use:

If rs.Supports(adBookmark) Then

This makes the application more robust across different providers and Recordset configurations.

13. Bookmark with Error Handling

A production application should account for situations where bookmark operations may fail.

Example:

Dim savedBookmark As Variant

If rs.Supports(adBookmark) Then
    savedBookmark = rs.Bookmark

    On Error Resume Next
    rs.MoveLast
    rs.Bookmark = savedBookmark

    If Err.Number <> 0 Then
        MsgBox "Unable to restore the previous record."
        Err.Clear
    End If

    On Error GoTo 0
End If

The exact error-handling strategy depends on the application, but the important principle is to avoid assuming that a previously stored bookmark will always remain valid.

14. Advantages of the Bookmark Property

The Bookmark property provides several advantages:

  1. It allows an application to save the current Recordset position.

  2. It makes it easy to return to a previously selected record.

  3. It avoids unnecessary sequential navigation.

  4. It is useful in data-entry and database user-interface applications.

  5. It can simplify navigation through large Recordsets.

  6. It helps preserve a user's current selection while other operations are performed.

  7. It provides a convenient mechanism for temporarily remembering a Recordset position.

15. Limitations of the Bookmark Property

There are also important limitations.

First, bookmark support is not guaranteed for every Recordset.

Second, bookmarks are generally associated with a particular Recordset instance and should not be treated as permanent database identifiers.

Third, changes to the Recordset, filtering, cursor behavior, provider implementation, or underlying data can affect bookmark usability.

Fourth, bookmarks should not be used as a replacement for primary keys when an application needs to uniquely identify database records.

16. Bookmark vs. Primary Key

Feature Bookmark Primary Key
Purpose Identifies a Recordset position Identifies a database row
Managed by ADO/provider Database
Stored in database No Yes
Permanent identifier Generally no Yes
Used for navigation Yes Indirectly
Guaranteed for every Recordset No Database-dependent but normally defined by schema
Suitable for long-term record identification No Yes

17. Complete Example

The following example demonstrates the basic concept:

Dim rs As ADODB.Recordset
Dim savedBookmark As Variant

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName FROM Employees", _
        connection, _
        adOpenStatic, _
        adLockOptimistic

If Not rs.EOF Then

    If rs.Supports(adBookmark) Then

        savedBookmark = rs.Bookmark

        Debug.Print rs.Fields("EmployeeName").Value

        rs.MoveLast

        Debug.Print rs.Fields("EmployeeName").Value

        rs.Bookmark = savedBookmark

        Debug.Print rs.Fields("EmployeeName").Value

    End If

End If

The sequence is:

  1. A Recordset is opened.

  2. The application checks whether bookmarks are supported.

  3. The current record's bookmark is saved.

  4. The Recordset moves to the last record.

  5. The saved bookmark is assigned back to the Recordset.

  6. The Recordset returns to the original record.

Conclusion

The ADO Recordset Bookmark property provides a convenient way to remember and restore the current position within a Recordset. Instead of repeatedly navigating through records using MoveFirst, MoveNext, or other movement methods, an application can save the current bookmark and later assign it back to the Recordset.

The most important points to remember are that a bookmark represents a Recordset position, not a database primary key; bookmark support should be checked using Supports(adBookmark); and bookmarks should generally be used as temporary navigation references rather than permanent record identifiers.