Using Bookmarks

When you work with database records you have to keep in mind that the actual number of records in a recordset can change at any time as new records are added or others are deleted. Therefore, you cannot save a record number to return to it later. Because records change all the time, the record numbers cannot be trusted. However, programmers often need to save the position of a record after they've moved to it or found it based on certain criteria. Instead of scrolling through every record in a recordset comparing the values, you can move directly to a specific record by using a bookmark. A bookmark is a value that uniquely identifies a row in a recordset.

Use the Bookmark property of the Recordset object to mark the record so you can return to it later. The Bookmark property is read-write, which means that you can get a bookmark for a record or set the current record in a Recordset object to the record identified by a valid bookmark. The recordset's Bookmark property always represents the current row. Therefore, if you need to mark more than one row for later retrieval, you may want to use an array to store multiple bookmarks (see Hands-On 13-13).

A single bookmark can be stored in a Variant variable. For example, when you get to a particular row in a recordset and decide that you'd like to save its location, store the recordset's bookmark in a variable, like this:

varMyBkmrk = rst.Bookmark

The varMyBkmrk is the name of a Variant variable declared with the following statement:

Dim varMyBkmrk As Variant

To retrieve the bookmark, move to another row, then use the saved bookmark to move back to the original row, like this:

rst.Bookmark = varMyBkmrk

Because not all ADO recordsets support the Bookmark property, you should use the Supports method to determine if the recordset does. Here's how:

If rst.Supports(adBookmark) then

MsgBox "Bookmarks are supported." Else

MsgBox "Sorry, can't use bookmarks!" End If

Creating and Manipulating Databases with ADO

Recordsets defined with a Static or Keyset cursor always support bookmarks. If you remove the adOpenKeyset intrinsic constant from the code used in the next procedure (Hands-On 13-13), the default cursor (adOpenForwardOnly) will be used and you'll get an error because this cursor does not support bookmarks.

Another precaution to keep in mind is that there is no valid bookmark when the current row is positioned at the new row in a recordset. For example, if you add a new record with the following statement:

rst.AddNew and then attempt to mark this record with a bookmark:

varMyBkmrk = rst.Book you will get an error.

When you close the recordset, bookmarks you've saved become invalid. Also, bookmarks are unique to the recordset in which they were created. This means that you cannot use a bookmark created in one recordset to move to the same record in another recordset. However, if you clone a recordset (that is, you create a duplicate Recordset object), a Bookmark object from one Recordset object will refer to the same record in its clone. (See the section called "Cloning a Recordset" in Chapter 16.)

0 0

Post a comment