Comparison of Seek Versus Find Methods, for VB Data Access

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

This article compares the Seek method to the find methods (FindFirst, FindLast, FindNext, and FindPrevious) for data access in Visual Basic.

MORE INFORMATION

The Seek and find methods differ in performance and in the type of recordsets to which they apply:

• The find methods (FindFirst, FindLast, FindNext, and FindPrevious) apply to Dynasets and Snapshots but not to Table objects. Conversely, the Seek method is available only on the Table object.
• The Seek method is significantly faster than the find methods. It is also more flexible because you can change the Index property of the Table object to change the order of the Seek. For intensive searches, you may want to create a Table object so that you can use the Seek method along with the find methods on the open Dynasets.

FindFirst, FindLast, FindNext, FindPrevious Methods

The FindFirst, FindLast, FindNext, and FindPrevious methods locate the first, last, next, or previous record, respectively, that satisfies the specified criteria and makes that record the current record. These methods are referred to as the find methods and have the following syntax:

   <recordset>.FindFirst <criteria>

   where <recordset> and <criteria> are defined as follows:

      <recordset> is the Recordset property of a data control or an object
      variable identifying a Dynaset or Snapshot.

      <criteria> is a string expression specifying the records that you
      want. The string is the WHERE clause in an SQL string without the
      word WHERE.

NOTE: With a data control, if an Edit or AddNew operation is pending when you use one of the find or move methods, the Update method is automatically invoked if not intercepted during the Validate event.

CAUTION: In the Professional Edition of Visual Basic, if you are not using a data control and use one of the find or move methods while an Edit or AddNew method is pending, any existing changes will be lost and no error will occur. An Edit or AddNew will be pending until an Update occurs. For more information, see the Update method in the Help menu.

Example Code for FindFirst Method

The following example creates a Dynaset, and then uses FindFirst to locate the first record satisfying the title condition:

   Sub Form_Load ()
      Dim MyCriteria As String, MyDB As Database, MySet As Dynaset
      MyCriteria = "State = 'NY'"    ' Define search criteria.
      Set MyDB = OpenDatabase("BIBLIO.MDB")
      ' Create a Dynaset based on the Publishers table:
      Set MySet = MyDB.CreateDynaset("Publishers")

      ' Find first matching record:
      MySet.FindFirst MyCriteria
      If Not MySet.NoMatch Then
         MsgBox "match was found"
      Else
         MsgBox "match was not found"
      End If  ' For a data control, you can use Data1.Recordset.NoMatch

      ' Find next matching record:
      MySet.FindNext MyCriteria
      If Not MySet.NoMatch Then
         MsgBox "match was found"
      Else
         MsgBox "match was not found"
      End If

   End Sub

Seek Method

The Seek method locates a record that meets the specified criteria for the current index in an indexed table and makes it the current record. The Seek method has the following syntax:

   <table>.Seek <comparison>, <key1>, <key2>, ...

   where the arguments are defined as follows:

      <comparison>          is one of the following string expressions:
                            <, <=, =, >=, >, or <>

      <key1>, <key2>, ...   one value for each field in the table's current
                            index.

Seek searches through the specified Table using the current index and locates the first record satisfying the criteria specified by comparison and the key values (key1, key2...) and makes it the current record.

You must set the current index with the Index property before you use Seek. If the index identifies a non-unique-key field, Seek locates the first record satisfying the criteria.

When the comparison is =, >=, >, or <>, Seek starts at the beginning of the index and searches forward. When the comparison is <= or <, Seek starts at the end of the index and searches backward.

If <table> doesn't refer to an open table, or if there is no current index, an error occurs.

Always inspect the value of the NoMatch property of the recordset to determine whether each Seek method has succeeded. If Seek fails, NoMatch is True and the current record is unchanged.

Example Code for Seek Method

The following example uses Seek to locate the first record in the Publishers table where the PubID field is 3, using the existing primary key index:

   Sub Form_Load ()
      Dim MyDB As database, MyTable As table
      Set MyDB = OpenDatabase("BIBLIO.MDB")      ' Open a database.
      Set MyTable = MyDB.OpenTable("Publishers") ' Open a table.
      MyTable.Index = "PrimaryKey"               ' Define current index.
      MyTable.Seek "=", 3                        ' Seek record.
      If MyTable.NoMatch Then
         MsgBox "match was not found"
      Else
         MsgBox "match was found"
      End If
   End Sub

REFERENCES

For more information, please read the Visual Basic online Help for the Seek, FindFirst, FindLast, FindNext, and FindPrevious Methods.

You can study the database design of a database file such as BIBLIO.MDB by opening it with Microsoft Access, or with the Data Manager or VISDATA provided with Visual Basic.

You can run the Data Manager program from the Window menu in Visual Basic, or from the Windows File Manager run DATAMGR.EXE in the Visual Basic directory.

The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. You can refer to the VISDATA source code for examples of how to use each data access function.