ACC: Seek Method Is Faster Than Find Method (98806)

SYMPTOMS

When you are using Visual Basic for Applications to find a record in an indexed field, the Seek method may be faster than the Find method, especially in a large table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

When you perform a Seek, you are opening a table directly and moving to the record based on an index value. When you create a dynaset and perform a Find, you are checking the value of the field in every record until you find a match.

Steps to Reproduce Behavior

Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0 or earlier).

Create a new module and enter the following two functions:

   
      '*********************************************
      'Declarations section of the module.
      '*********************************************
      Option Explicit

      '=============================================
      'The following function uses the Seek method.
      'It quickly returns the Customer ID where the
      '   PrimaryKey field = 11070.
      '=============================================
      Function Faster ()
         Dim db As Database, tbl As Recordset

         Set db = CurrentDB()
         Set tbl = db.OpenRecordset("Orders", DB_OPEN_TABLE)

         tbl.Index = "PrimaryKey"
         tbl.Seek "=", 11070
         Debug.Print tbl("CustomerID")

         ' NOTE: In Microsoft Access 2.0 or earlier, there is a space in
         ' Customer ID.

         tbl.Close
      End Function

      '=============================================
      'The following function uses the Find method.
      'It is slightly slower in returning the Customer
      '   ID where the PrimaryKey field = 11070.
      '=============================================
      Function Slower()
         Dim Criteria As String, MyDB As Database, Myset As Recordset

         Set MyDB = CurrentDB()
         Set Myset = MyDB.OpenRecordset("Orders", DB_OPEN_DYNASET)
         Criteria = "[OrderID] =" & 11070

         ' NOTE: In Microsoft Access 2.0 or earlier, there is a space in
         ' Order ID.

         Myset.FindNext Criteria
         Debug.Print Myset("Customerid")
      End Function

On the View menu, click Debug window (or Immediate window in Microsoft Access 2.0 or earlier).
In the Debug window, type the following line, and then press ENTER:
?Faster()
Then, type the following line, and press ENTER:
?Slower()
Notice that the Faster() function, which uses Seek, is slightly faster than the Slower() function, which uses FindNext.