ACC: Seek Method Is Faster Than Find Method

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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

RESOLUTION

Use the Seek method on indexed fields to optimize your search speed.

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

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

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

3. On the View menu, click Debug window (or Immediate window in Microsoft Access 2.0 or earlier).

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

REFERENCES

For more information about using the Seek method, search the Help Index for "Seek."

For more information about using the Find method, search the Help Index for "FindFirst."