Navigating Within a Recordset

A Recordset object has a current position. The position may be before the first record (BOF), after the last record (EOF), or on a specific record within the Recordset. When retrieving information with the Field object, the information always pertains to the record at the current position.

Moving To Another Record

Both DAO and ADO contain several methods for moving from one record to another. These methods are Move, MoveFirst, MoveLast, MoveNext, and MovePrevious.

The following listings demonstrate how to use the MoveNext method to iterate through all of the records in the Recordset.

DAO

Sub DAOMoveNext()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")

' Open the Recordset
Set rst = db.OpenRecordset _
  ("SELECT * FROM Customers WHERE Region = 'WA'", _
  dbOpenForwardOnly, dbReadOnly)

' Print the values for the fields in
' the first record in the debug window
Do Until rst.EOF
  For Each fld In rst.Fields
     Debug.Print fld.Value & ";";
  Next
  Debug.Print
  rst.MoveNext
Loop

' Close the recordset
rst.Close

End Sub

ADO

Sub ADOMoveNext()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
  & "Data Source=.\nn.mdb;"

' Open the forward-only,
' read-only recordset
rst.Open _
  "SELECT * FROM Customers WHERE Region = 'WA'", _
  cnn, adOpenForwardOnly, adLockReadOnly

' Print the values for the fields in
' the first record in the debug window
Do Until rst.EOF
  For Each fld In rst.Fields
     Debug.Print fld.Value & ";";
  Next
  Debug.Print
  rst.MoveNext
Loop

' Close the recordset
rst.Close

End Sub

Notice that the code for iterating through the Recordset in DAO and ADO is identical.

ADO Shortcuts to Display and Open Recordsets

In the previous example, the ADO code could be rewritten to use the Recordset object's GetString method to print the data to the Debug window. This method returns a formatted string containing data from the records in the recordset. Using GetString, the While loop in the previous ADO example could be replaced with the single line:

   Debug.Print rst.GetString(adClipString, , ";")

This method is handy for debugging as well as populating grids and other controls that allow you to pass in a formatted string representing the data. GetString is also faster than looping through the Recordset and generating the string with Visual Basic for Applications code.

The ADO example could also have been rewritten more concisely by using the Recordset object's Open method's ActiveConnection parameter to specify the connection string rather than first opening a Connection object and then passing that object in as the ActiveConnection. The Recordset object's Open method call would look like this:

   rst.Open _
     "SELECT * FROM Customers WHERE Region = 'WA'", _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=.\nn.mdb;", _
     adOpenForwardOnly, adLockReadOnly

Internally, these two mechanisms are essentially the same. When you pass a connection string to the Recordset object's Open method (rather than assigning a Connection object to the Recordset object's ActiveConnection property), ADO creates a new, internal Connection object. If you plan on opening more than one Recordset from a given data source, or opening Command or Catalog objects, create a Connection object and use that object for the ActiveConnection. This will reduce the amount of resources consumed and increase performance.

Determining Current Position

When working with records in a Recordset it may be useful to know what the record number of the current record is. Both ADO and DAO have an AbsolutePosition property that can be used to determine the record number. The following code listings demonstrate how to use the AbsolutePosition property in both DAO and ADO.

DAO

Sub DAOGetCurrentPosition()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset

  ' Open the database
  Set db = DBEngine.OpenDatabase(".\nn.mdb")

  ' Open the Recordset
  Set rst = db.OpenRecordset("SELECT * FROM Customers", _
     dbOpenDynaset)

  ' Print the absolute position
  Debug.Print rst.AbsolutePosition

  ' Move to the last record
  rst.MoveLast

  ' Print the absolute position
  Debug.Print rst.AbsolutePosition

  ' Close the recordset
  rst.Close

End Sub

ADO

Sub ADOGetCurrentPosition()

  Dim cnn As New ADODB.Connection
  Dim rst As New ADODB.Recordset

  ' Open the connection
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=.\nn.mdb;"

  ' Open the recordset
  rst.CursorLocation = adUseClient
  rst.Open "SELECT * FROM Customers", cnn, adOpenKeyset, _
     adLockOptimistic, adCmdText

  ' Print the absolute position
  Debug.Print rst.AbsolutePosition

  ' Move to the last record
  rst.MoveLast

  ' Print the absolute position
  Debug.Print rst.AbsolutePosition

  ' Close the recordset
  rst.Close

End Sub

The ADO and DAO code for determining the current position within the Recordset looks very similar. However, note that the results printed to the debug window are different. With DAO, the AbsolutePosition property is zero-based; the first record in the recordset has an AbsolutePosition of 0. With ADO, the AbsolutePosition property is one-based; the first record in the recordset has an AbsolutePosition of 1.

Note that in the ADO code example, the CursorLocation property is set to adUseClient. If the CursorLocation is not specified or is set to adUseServer, the AbsolutePosition property will return adUnknown (-1) because the Microsoft Jet Provider does not support retrieving this information.

In addition to the AbsolutePosition property, DAO also has a PercentPosition property that returns a percentage representing the approximate position of the current record within the Recordset. ADO does not have a property or method that provides the functionality equivalent to DAO's PercentPosition property. However, when using client cursors (adUseClient), the user can calulate an approximate percent position from the AbsolutePosition and RecordCount properties in ADO.