Finding Records in a Recordset with DAO and ADO

both DAO and ADO have two mechanisms for locating a record in a Recordset: Find and Seek. With both mechanisms you specify criteria to use to locate a matching record. In general, for equivalent types of searches, Seek provides better performance than Find. However, because Seek uses an underlying index to locate the record, it is limited to Recordset objects that have associated indexes. For Microsoft Jet databases only, Recordset objects based on a table (DAO dbOpenTable, ADO adCmdTableDirect) with an index support Seek.

Using the Find Method

The following listings demonstrate how to locate a record using Find.

DAO

Sub DAOFindRecord()

  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("Customers", dbOpenDynaset)

  ' Find the first customer whose country is USA
  rst.FindFirst "Country = 'USA'"

  ' Print the customer id's of all customers in the USA
  Do Until rst.NoMatch
     Debug.Print rst.Fields("CustomerId").Value
     rst.FindNext "Country = 'USA'"
  Loop

  ' Close the recordset
  rst.Close

End Sub

ADO

Sub ADOFindRecord()

   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.Open "Customers", cnn, adOpenKeyset, adLockOptimistic

   ' Find the first customer whose country is USA
   rst.Find "Country='USA'"

   ' Print the customer id's of all customers in the USA
   Do Until rst.EOF
      Debug.Print rst.Fields("CustomerId").Value
      rst.Find "Country='USA'", 1
   Loop

   ' Close the recordset
   rst.Close

End Sub

DAO includes four find methods: FindFirst, FindLast, FindNext, FindPrevious. You choose which method to use based on the point from which you want to start searching (beginning, end, or curent record) and in which direction you want to search (forward or backward).

ADO has a single method: Find. Searching always begins from the current record. The Find method has parameters that allow you to specify the search direction as well as an offset from the current record at which to beginning searching (SkipRows). The following table shows how to map the four DAO methods to the equivalent functionality in ADO.

DAO method	ADO Find with SkipRows	ADO search direction
FindFirst	0	adSearchForward (if not currently positioned on the first record, call MoveFirst before Find)
FindLast	0	adSearchBackward (if not currently positioned on the last record, call MoveLast before Find)
FindNext	1	adSearchForward
FindPrevious	1	adSearchBackward

DAO and ADO require a different syntax for locating records based on a Null value. In DAO if you want to find a record that has a Null value you use the following syntax:

"ColumnName Is Null"

or, to find a record that does not have a Null value for that column:

"ColumnName Is Not Null"

ADO, however, does not recognize the Is operator. You must use the = or <> operators instead. So the equivalent ADO criteria would be:

"ColumnName = Null"

or

"ColumnName <> Null"

So far, each of the criteria shown in the examples above are based on a value for a single column. However, with DAO, the Criteria parameter is like the WHERE clause in an SQL statement and can contain multiple columns and compare operators within the criteria.

This is not the case with ADO. The ADO Criteria parameter is a string containing a single column name, comparison operator, and value to use in the search. If you need to find a record based on multiple columns, use the Filter property (see the section, "Filtering and Sorting Data") to create a view of the Recordset that only contains those records matching the criteria.

DAO and ADO behave differently if a record that meets the specified criteria is not found. DAO sets the NoMatch property to True and the current record is not defined. If ADO does not find a record that meets the criteria, the current record is positioned either before the beginning of the Recordset if searching forward (adSearchForward) or after the end of the Recordset if searching backward (adSearchBackward). Use the BOF or EOF properties as appropriate to determine whether or not a match was found.

Using the Seek Method

The following listings demonstrate how to locate a record using Seek.

DAO

Sub DAOSeekRecord()

  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("Order Details", dbOpenTable)

  ' Select the index used to order the data in the recordset
  rst.Index = "PrimaryKey"

  ' Find the order where OrderId = 10255 and ProductId = 16
  rst.Seek "=", 10255, 16

  ' If a match is found print the quantity of the order
  If Not rst.NoMatch Then
     Debug.Print rst.Fields("Quantity").Value
  End If

  ' Close the recordset
  rst.Close

End Sub

ADO

Sub ADOSeekRecord()

  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.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _
     adCmdTableDirect

  ' Select the index used to order the data in the recordset
  rst.Index = "PrimaryKey"

  ' Find the order where OrderId = 10255 and ProductId = 16
  rst.Seek Array(10255, 16), adSeekFirstEQ

  ' If a match is found print the quantity of the order
  If Not rst.EOF Then
  Debug.Print rst.Fields("Quantity").Value
  End If

  ' Close the recordset
  rst.Close

End Sub

Because Seek is based on an index, it is important to specify an index before searching. In the previous example, this is not strictly necessary because Microsoft Jet will use the primary key if an index is not specified.

In the ADO example, the Visual Basic for Applications Array function is used when specifying a value for more than one column as part of the KeyValues parameter. If only one value is specified, it is not necessary to use the Array function.

As with the Find method, use the NoMatch property with DAO to determine whether or not a matching record was found. Use the BOF and EOF properties as appropriate with ADO.

The Seek method will work correctly only for Microsoft Jet 4.0 databases. It will fail with a run-time error for all earlier formats, even if you use the Microsoft Jet 4.0 database engine to open the database. This will cause a problem if the application is written to support older database formats. If so, use the Supports method of the Recordset object to determine whether the Seek method is available for the open Recordset. However, if all client applications use the newer format, this check is unnecessary. Use either Microsoft Access 2000 or the CompactDatabase method to convert older databases to the newer format.