The ActiveX Data Objects (ADO) Recordset object has methods and properties
that allow you to use it to perform FoxPro-style record navigation.
When you open a Recordset object, each record is assigned a unique
bookmark. You can use the Bookmark property to save the location of the
current record so you can return to it at a later time.
Not all Recordset objects support bookmarks. For example, forward-only and
dynamic cursors do not support the use of bookmarks. You can use the
Supports property of the recordset to determine if it supports bookmarks.
The EOF property is set to true when the user tries to move past the last
record of a Recordset.
The BOF property is set to true when the user tries to move to a location
before the first row in the Recordset. The next attempt to MovePrevious
results in a run-time error. Both BOF and EOF are set to true if the
recordset is empty.
The various move methods (MoveFirst, MoveLast, MoveNext and MovePrevious)
are similar in effect to the FoxPro SKIP and GOTO commands.
To use this example, you must have Microsoft Data Access Components (MDAC)
version 2.x or later installed, which is included in the data components of
Visual Studio 6.0 or it can be downloaded from the following Web address:
The following example establishes a DSN-less connection and creates a
Recordset based on a query of the sample SQL Server AUTHORS table. It uses
the various Move methods of the Recordset and tests the BOF and EOF
properties.
Sample Code
#DEFINE adUseClient 3
oRecordSet = CREATEOBJECT("ADODB.Recordset")
lcSQL = "Select * from authors"
* Modify the SERVER=, DATABASE=, UID= and PWD= parameters
* as needed.
WITH oRecordSet
.CursorLocation = adUseClient
.OPEN(lcSQL, ;
"DRIVER={SQL Server};" + ;
"SERVER=YourServerName;" + ;
"DATABASE=pubs;" + ;
"UID=YourUserID;PWD=YourPassword")
ENDWITH
CLEAR
oRecordSet.MoveLast
? "Last record last name: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.MoveFirst
? "First record last name: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.MovePrevious
IF oRecordSet.BOF
? "Can't move prior to BOF - going to top"
oRecordSet.MoveFirst
ENDIF
oRecordSet.MoveNext
? "Second record last name: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.MoveLast
oRecordSet.MoveNext
IF oRecordSet.EOF
? "Can't move past EOF - going to bottom"
oRecordSet.MoveLast
ENDIF
oRecordSet.MovePrevious
? "Next-to-last record last name: ", oRecordSet.FIELDS("au_lname").VALUE
oBookmark = oRecordSet.Bookmark
? "Bookmark: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.MoveFirst
? "Move to the top: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.Bookmark = oBookmark
? "Return to Bookmark: ", oRecordSet.FIELDS("au_lname").VALUE
oRecordSet.CLOSE