ACC2000: Seek Method Is Faster Than Find Method (210545)

MORE INFORMATION

Steps to Reproduce the Behavior

1. Open the sample database Northwind.mdb.
2. Create a new module and paste or type the following code:

   Function MakeTestTable()
   Dim dbs As Database, tdf As TableDef
   Dim idx As Index, fld As Field
   
   Set dbs = CurrentDb()
   
   DoCmd.SetWarnings False
   
   'Run a make table query copying Orders to BigOrder.
   DoCmd.RunSQL "SELECT Orders.OrderID, Orders.CustomerID, " _
                & "Orders.EmployeeID, Orders.OrderDate, " _
                & "Orders.RequiredDate, Orders.ShippedDate, " _
                & "Orders.ShipVia, Orders.Freight, Orders.ShipName, " _
                & "Orders.ShipAddress, Orders.ShipCity, " _
                & "Orders.ShipRegion, Orders.ShipPostalCode, " _
                & "Orders.ShipCountry INTO BigOrders FROM Orders;"
   
   ' Make table queries do not copy indexes, so a new one
   ' must be created.
   
   ' Open the table definition.
   Set tdf = dbs.TableDefs("BigOrders")
   
   ' Create an index called PrimaryKey for this TableDef
   ' and turn on the Primary and Required properties.
   
   Set idx = tdf.CreateIndex("PrimaryKey")
   
   With idx
       .Name = "PrimaryKey"
       .Primary = True
       .Required = True
       .IgnoreNulls = False
   End With
   
   ' Create an index field with the same name as a table field,
   ' then append it to the index.
   Set fld = idx.CreateField("OrderID")
   idx.Fields.Append fld
   
   ' Append the new index to the TableDef.
   tdf.Indexes.Append idx
   
   'Append the records of the Orders table to the new Big Orders table
   '300 times, thus making a large table for testing the functions.
   For I = 1 To 300
       DoCmd.RunSQL "INSERT INTO BigOrders ( CustomerID, EmployeeID, " _
       & "OrderDate, RequiredDate, ShippedDate, ShipVia, Freight,  " _
       & "ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,  " _
       & "ShipCountry ) SELECT Orders.CustomerID, Orders.EmployeeID,  " _
       & "Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,  " _
       & "Orders.ShipVia, Orders.Freight, Orders.ShipName, " _
       & "Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, " _
       & "Orders.ShipPostalCode,  Orders.ShipCountry FROM Orders;"
   Next I
   
   DoCmd.SetWarnings True
   
   End Function
   
   '=============================================
   'The following function runs the Seek method.
   'It quickly returns the Customer ID where the
   'PrimaryKey field = 260066.
   '=============================================
   
   Function FastSeek()
      Dim db As Database, tbl As Recordset
   
      Set db = CurrentDb()
      Set tbl = db.OpenRecordset("BigOrders", DB_OPEN_TABLE)
   
      tbl.Index = "PrimaryKey"
      tbl.Seek "=", 260066
      Debug.Print tbl("CustomerID")
   
      tbl.Close
   End Function
   
   '=============================================
   'The following function uses the Find method.
   'It is slightly slower in returning the Customer
   '   ID where the PrimaryKey field = 260066.
   '=============================================
   Function SlowFindNext()
      Dim Criteria As String, MyDB As Database, Myset As Recordset
   
      Set MyDB = CurrentDb()
      Set Myset = MyDB.OpenRecordset("BigOrders", DB_OPEN_DYNASET)
      Criteria = "[OrderID] =" & 260066
   
      Myset.FindNext Criteria
      Debug.Print Myset("Customerid")
   End Function
   					

3. Type the following line in the Immediate window, and then press ENTER:

   ?MakeTestTable()
   						

   NOTE: You may have to wait several seconds for this function to create the test table.
4. Type the following line, and then press ENTER:

   ?FastSeek()
   					

5. Type the following line, and then press ENTER:

   ?SlowFindNext()