Bookmarking and Moving through Records with ADO

Introduction

Moving through the recordset is often required while, for example, trying to locate a record in order to review it or make changes to it. The ability to come back to a given record after traversing the records is equally important. ADO supports this ability through several of its methods. However, since it has several ways of supporting the functionality of recordsets, this ability to move freely is restricted to those cursor types that support free movement

This tutorial will assume that the reader has reviewed the first three tutorials on ADO's connection object and the recordset object (listed below). Only a few of the properties of the recordset object were described; in the present tutorial you will see a number of methods related to navigation through the data. This tutorial also shows how you may bind the data to controls which are unbound at design time using code.

The first three tutorials on ADO include "Cursors in MS Access and ADO," "Using the Recordset with MS Access and ADO," and "Connecting to Microsoft Access with ADO," with the most recent one listed first. Please click on the links if you need to refresh your memory or have not read these articles before. 

Here is a bulleted review of properties and methods from the three previous tutorials. Some aspects of moving the data were discussed to clarify the BOF, EOF properties in the context of cursor type property. In this tutorial a few more properties needed for bookmarking records and moving through the records will be discussed.

• Connection Open()
• Connection Close()
• Connection Version
• Connection State
• Connection ConnectionTimeout
• Recordset Status
• Recordset CursorType
• Recordset CursorLocation
• Recordset ActiveConnection
• Recordset AbsolutePosition
• Recordset LockType
• Recordset RecordCount
• Recordset BOF
• Recordset EOF
• Recordset MoveNext
• Recordset MovePrevious
• Recordset MoveFirst

The recordset returned by a query is the result of the query as shown in the next picture. You may access the fields by using the recordset's Fields collection and going through each of them as shown in the code. For accessing rows you may depend on the fact that the recordset opens with the current record with Absolute Position = 1 . Now you can use MoveNext to go the second record, and so on forward until you reach the last record. You will end the process when you reach the EOF. For all these positions you will collect the fields as discussed earlier.

The fields of the recordset object

The following code shows how you access the fields collection using an index. The picture that follows shows some of the values of the field (synonymous with a column). After you open a recordset and run this code, you will be accessing the first row. If you want to get the fields for, say,  Absolute Position=2 , just insert a rst.MoveNext after you open the recordset and before you go through the For...Next block.

Private Sub Command9_Click()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
Debug.Print rst.Fields.Count
strfld = ""
For i = 0 To rst.Fields.Count - 1
strfld = strfld + "Item.Value: " & rst.Fields.Item(i).Value & vbCrLf
strfld = strfld + "Item.Type: " & rst.Fields.Item(i).Type & vbCrLf
strfld = strfld + "Item.Name: " & rst.Fields.Item(i).Name & vbCrLf
strfld = strfld + "Item.Definedsize: " & rst.Fields.Item(i).

DefinedSize & vbCrLf
strfld = strfld + "-----------------------------------" & vbCrLf
Next
Text10.SetFocus
Text10.Text = strfld
rst.Close
cnn.Close
End Sub

The rows of the recordset

You will implement the above code from Absolute Position = 1 up to EOF using the following code in a DO LOOP.

Private Sub Command12_Click()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
strrows = ""
Do While rst.EOF = False
strrows = strrows + rst.Fields.Item(1) & "  |  " & rst.Fields.Item(2) 

& vbCrLf
strrows = strrows + "---------------------------" & vbCrLf
Text10.SetFocus
Text10.Text = strrows
rst.MoveNext
Loop
End Sub

In the above code, the various items of the fields collection (here only two items were collected) are accessed inside a Do Loop and at the end of the record you move the pointer to the next record using a MoveNext command. Again for display, you will gather them in a string and dump the string to a text box as shown in the following picture.

Moving through the Recordset and Bookmarking

Moving the cursor from one record to another gives us the ability to cycle through the records one after another. The databound controls leverage this ability to display all the columns for a given row of data. Moving the record pointer is facilitated by the various listed Move methods. The code at the end of this section in a Form_Load() event of a form shows how you may move to records as well as bookmark them along the way. The following methods will be considered with some built in constants for the Bookmark.

• Move(<number of records>,start)
• MoveFirst
• MovePrevious
• MoveNext
• MoveLast
• Bookmark

The Move method takes two arguments, the first being the number of records to move through and the second parameter called Start. It's the record to start from. It could also represent a record that has already been bookmarked, or any of the constants adBookMarkFirst, adBookmarkLast, and adBookmarkCurrent. You will see how the bookmark is established shortly. You will also see the how the Move method is called with the arguments. The first argument, which is a number, could be negative as well, but care has to be exercised in using it. With number=0 nothing happens.

These 'moves' do exactly what they say. They do not take any arguments. You have to be careful using these because some movements are not allowed for certain  CursorTypes. For example, for the forward only cursor you cannot obviously call 'MovePrevious,' or 'MoveFirst' when you are looking at record 2. For movements allowed by cursor types, refer to the second article of the tutorial. You must also exercise caution when you are on the first and last records and trying to use the MovePrevious and MoveNext methods as they fall in the BOF and EOF areas. In these cases you can come back to a previous bookmark you have established without aborting the program.

The MoveFirst and MoveLast methods take you to the first and last records in the recordset. The bookmark property lets you mark a recordset you may want to go back to, very much like a bookmark in a book you are reading. This means once you have bookmarked a record, you can retrieve it later. When you set the bookmark, the current record pointer will be set to that record. The absolute value of the bookmark is seldom needed; all that is needed is to go back to that record, should you need to. Recordset will not retain the bookmark property if you close and open again, or if you call the Requery. You may also use the values adBookMarkFirst, adBookMarkLast, and adBookMarkCurrent for the Start parameter. You will find an example of this in the code that uses adBookmarkFirst.

The following code shows how to set a bookmark and how you may retrieve it. It also shows how to move between the records with the move methods. You may not be able to cut and paste the code in MS Access because there are formatting elements in the text.

Private Sub Form_Load()
'Example for Bookmark
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With

End Sub

Binding the recordset to Controls on the form

In an earlier tutorial we discussed the new property of a form, namely the Recordset. By assigning the recordset under investigation to Me.Recordset you will be creating a record source for all objects that you place on the form. Review the following code that assigns each of the fields to a control (in this case a Text box). The record navigation on the form will be available to you unless you turn it off in design.

Drag and drop four text box controls. Text0 to Text6 are the text box controls in this tutorial. Change the label caption of the text box controls so that they are indicative of the column names and implement the code as shown. When you open this form, you will see the first record filling the text boxes. You can now use the navigation to move forwards and backwards.

Private Sub Form_Load()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
Set Me.Recordset = rst
Me.Text0.ControlSource = rst.Fields.Item(0).Name
Me.Text2.ControlSource = rst.Fields.Item(1).Name
Me.Text4.ControlSource = rst.Fields.Item(2).Name
Me.Text6.ControlSource = rst.Fields.Item(3).Name
MsgBox (rst.Fields.Count)
Set rst = Nothing
Set cnn = Nothing
End Sub 

Summary

In this tutorial we have seen some of the basic features of ADO's recordset as applied to data retrieved using a simple select statement on a table in the Northwind sample database. As discussed in the previous tutorial not all cursors support the Bookmark property. When in doubt use the 'Supports' property of recordset. The CursorOptionEnums which show what can or cannot be done is a function of the data provider, the CursorType, CursorLocation and the LockType. Using the recordset property of a Form you can see how easily you can bind data to the controls on the form.