Microsoft KB Archive/108149

= Comparison of Seek Versus Find Methods, for VB Data Access =

Article ID: 108149

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q108149



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:

  .FindFirst

where and are defined as follows:

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

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

If the recordset contains more than one record that matches the criteria, FindFirst locates the first occurrence, FindNext locates the next occurrence, and so on. You can follow a find method with a move method, such as MoveNext, which moves to the next record regardless of whether it matches any criteria. If no matching records are found, the NoMatch property is True and the current record remains the same as it was before the find method was used.

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:

  .Seek, , , ...

where the arguments are defined as follows:

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

,, ...  one value for each field in the table's current index.

To use the Seek method, you must first use the OpenTable method to create an object variable for the table.

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 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

