ADO - ADO Bookmark Property and Record Navigation

The Bookmark Property in ADO (ActiveX Data Objects) is used to identify and return to a specific record within a Recordset. When an application retrieves multiple records from a database, the user or program may move forward and backward through those records. A bookmark provides a convenient way to remember the position of a particular record so that the application can return to it later. Instead of searching through the Recordset again, the application can save the bookmark value associated with the record and use it when needed.

Understanding the Bookmark Property

The Bookmark property belongs to the ADO Recordset object. It represents the location of the current record in a Recordset. When the current record changes, the Bookmark property changes accordingly. An application can store the current bookmark in a variable and later assign that value back to the Bookmark property to return to the same record.

A simplified example is:

Dim savedBookmark

savedBookmark = rs.Bookmark

'Move to another record
rs.MoveNext

'Return to the previously saved record
rs.Bookmark = savedBookmark

In this example, the bookmark of the current record is stored in savedBookmark. After moving to another record, assigning the saved value back to rs.Bookmark returns the Recordset to the previously bookmarked record.

Why Bookmarks Are Useful

Bookmarks are particularly useful when an application needs to temporarily move away from a record and later return to it. For example, consider a customer-management application displaying a list of customers. A user may select one customer, move through other records to examine additional information, and then return to the originally selected customer.

Without a bookmark, the application might have to identify the customer again using another search operation. With a bookmark, the application can simply save the current position and restore it when necessary.

Bookmarks can therefore make record navigation easier in applications that frequently move between records.

Saving a Bookmark

A bookmark can be saved before changing the current record. For example:

Dim customerBookmark

customerBookmark = rs.Bookmark

The variable now contains the bookmark associated with the current record. The application can perform other navigation operations and later use the saved value.

rs.Bookmark = customerBookmark

This moves the current position back to the bookmarked record.

The bookmark should generally be treated as an opaque value. The application should not attempt to interpret the internal contents of the bookmark because its representation depends on the ADO provider and Recordset implementation.

Bookmark and Record Navigation

ADO provides several navigation methods for moving through a Recordset, such as MoveFirst, MoveLast, MoveNext, and MovePrevious. These methods change the current record sequentially or move to the beginning or end of the Recordset.

A bookmark provides a different type of navigation. Instead of moving to a record based on its relative position, the application can return directly to a previously saved record.

For example:

rs.MoveFirst
savedBookmark = rs.Bookmark

rs.MoveLast

rs.Bookmark = savedBookmark

The application first moves to the first record and saves its bookmark. It then moves to the last record. Finally, it restores the saved bookmark, returning to the first record.

This is useful when the exact position of a record needs to be preserved while other navigation takes place.

Bookmark Support

Not every ADO Recordset necessarily supports bookmarks. Whether bookmarks are available depends on the type of Recordset and the underlying data provider.

An application can check the Recordset's Bookmark capability before relying on bookmarks. In classic ADO, the Supports method can be used to determine whether a particular Recordset supports a capability.

For example:

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

If bookmark support is unavailable, the application should use another method of identifying the record, such as a unique database key.

This is important because assuming that every Recordset supports bookmarks can cause runtime problems when working with different providers.

Bookmark Versus Database Primary Key

A bookmark should not be confused with a database primary key.

A primary key is a persistent identifier stored as part of the database design. For example, a customer table might contain:

CustomerID
101
102
103

The CustomerID can be used to identify a customer independently of a particular Recordset.

A bookmark, on the other hand, is primarily a navigation reference within an ADO Recordset. It is intended to help the application return to a particular record in that Recordset.

For long-term identification of database records, a primary key is generally more appropriate. For temporary navigation within a Recordset, a bookmark can be very convenient.

Important Considerations

Bookmarks should not normally be stored permanently and expected to remain valid indefinitely. Their usefulness is associated with the Recordset from which they were obtained. Closing the Recordset or recreating it can make a previously stored bookmark unsuitable for use.

Applications should also be careful when the underlying Recordset changes significantly. Depending on the provider and Recordset configuration, operations that modify the data or reconstruct the Recordset may affect the validity or usability of a previously saved bookmark.

Therefore, bookmarks are best viewed as temporary navigation references, not permanent record identifiers.

Practical Example

Consider an employee application containing a Recordset with employee information:

Set rs = CreateObject("ADODB.Recordset")

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

If rs.Supports(adBookmark) Then

    'Save the current record
    savedBookmark = rs.Bookmark

    'Navigate to another record
    rs.MoveNext

    'Perform some processing

    'Return to the saved record
    rs.Bookmark = savedBookmark

End If

The example demonstrates the basic workflow:

  1. Open a Recordset.

  2. Check whether bookmark functionality is supported.

  3. Save the current record's bookmark.

  4. Navigate to another record.

  5. Perform other operations.

  6. Restore the bookmark.

  7. Return to the original record.

Advantages of the Bookmark Property

The Bookmark property offers several benefits in record-navigation scenarios. It allows an application to preserve a user's current position, return to a previously visited record without performing another search, and simplify navigation logic. It can also be useful in applications where users temporarily move between records while editing or reviewing information.

However, bookmarks should be used only when the Recordset and provider support them reliably. When an application needs a stable identifier that can be used across different Recordsets or application sessions, a database key is generally a better choice.

Conclusion

The ADO Bookmark property provides a mechanism for temporarily remembering the current position of a record within a Recordset. By saving a bookmark before navigating elsewhere and restoring it later, an application can efficiently return to a previously selected record. It is especially useful for interactive database applications where users frequently move between records.

The key distinction to remember is that a bookmark is primarily a Recordset navigation mechanism, whereas a primary key is a database-level record identifier. Understanding this distinction helps developers choose the appropriate technique for reliable record navigation and record identification.