Microsoft KB Archive/98806

= ACC: Seek Method Is Faster Than Find Method =

Article ID: 98806

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q98806



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



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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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
 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(&quot;Orders&quot;, DB_OPEN_TABLE)

tbl.Index = &quot;PrimaryKey&quot; tbl.Seek &quot;=&quot;, 11070 Debug.Print tbl(&quot;CustomerID&quot;)

' 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(&quot;Orders&quot;, DB_OPEN_DYNASET) Criteria = &quot;[OrderID] =&quot; & 11070

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

Myset.FindNext Criteria Debug.Print Myset(&quot;Customerid&quot;) 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.

