How to find a record using ADO and Jet OLE DB provider in Access 2000

MORE INFORMATION

What follows are two example procedures. The first, CreateJetDB, creates a new Microsoft Jet database in the root directory of drive C and populates it with data. The second, CursorLocationTimed, demonstrates using the Find method with a server side cursor and with a client side cursor.

To create these procedures, follow these steps:

1. Create a new Microsoft Access database.
2. Create a new module.
3. On the Tools menu, click References, and make sure the following references are selected:

   Microsoft ActiveX Data Objects 2.1 Library
   Microsoft ADO Ext. 2.1 for DDL and Security

4. Type the following procedures:

   Sub CreateJetDB()
   
      Dim cat As ADOX.Catalog
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim numrecords As Long
      Dim i As Long
    
      Set cat = New ADOX.Catalog
      Set cn = New ADODB.Connection
      Set rs = New ADODB.Recordset
   
      ' Number of sample records to create
      numrecords = 250000
      
      On Error Resume Next
      
      'Delete the sample database if it already exists.
      Kill "c:\findseek.mdb"
      On Error GoTo 0
      
      ' Create a new Jet 4.0 database name findseek.mdb
      
      cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\findseek.mdb"
      
      ' Set the provider, open the database,
      'and create a new table called tblSequential.
      
      cn.Provider = "Microsoft.Jet.OLEDB.4.0"
      cn.Open "Data Source=c:\findseek.mdb"
      cn.Execute "CREATE TABLE tblSequential (col1 long, col2 text(75));"
      
      'Open the new table.
      rs.Open "tblSequential", cn, adOpenDynamic, _
      adLockOptimistic, adCmdTableDirect
      
      ' Add sample records to the tblSequential table.
      For i = 0 To numrecords
         rs.AddNew
         rs.Fields("col1").Value = i
         rs.Fields("col2").Value = "value_" & i
         rs.Update
         Next i
      rs.Close
      
      'Create a multifield Index on col1 and col2.
      cn.Execute "CREATE INDEX idxSeqInt on tblSequential (col1, col2);"
      
      'Close the connection
      cn.Close
   
   End Sub
   
   Sub CursorLocationTimed()
   
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim i, j As Long
      Dim time As Variant
      
      Set cn = New ADODB.Connection
      Set rs = New ADODB.Recordset
      
      On Error GoTo ErrHandler
      
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
      &  "Data Source=c:\findseek.mdb"
      
      ' Specify how ADO should open the recordset:
      ' adUseServer - use the native provider to perform cursor
      ' operations
      ' adUseClient - use the client cursor engine in ADO
      ' NOTE: adUseServer more closely resembles DAO
      
      ' Time opening a recordset and doing 1000 finds (Server cursor
      ' engine)
      '
      rs.CursorLocation = adUseServer
      time = Timer
      
      ' Open the recordset and perform serveral Finds to locate records.
      ' Using the adCmdTableDirect opens a base table against Jet, which
      ' is generally the fastest, most functional way to access tables.
      
      rs.Open "tblSequential", cn, adOpenDynamic, adLockOptimistic, _
      adCmdTableDirect
      
      For i = 0 To 1000
         rs.Find "col1=" & i
      Next i
      
      Debug.Print "Sequential Find + Open (Server) = " & Timer - time
      rs.Close
      
      ' Time opening a recordset and doing 1000 finds (Client cursor
      ' engine)
      
      rs.CursorLocation = adUseClient
      time = Timer
      
      rs.Open "tblSequential", cn, adOpenDynamic, _
      adLockOptimistic, adCmdTableDirect
      
      For i = 0 To 1000
         rs.Find "col1=" & i
      Next i
      
      Debug.Print "Sequential Find + Open (Client) = " & Timer - time
      rs.Close
      
      Exit Sub
   
   ErrHandler:
   
      For j = 0 To cn.Errors.Count - 1
         Debug.Print "Conn Err Num : "; cn.Errors(j).Number
         Debug.Print "Conn Err Desc: "; cn.Errors(j).Description
      Next j
      
      Resume Next
   
   End Sub
   					

5. To create the sample database, type the following line in the Immediate window, and then press ENTER:

   CreateJetDB
   					

6. To demonstrate the Find method, type the following line in the Immediate window, and then press ENTER:

   ?CursorLocationTimed()
   						

   You should next see output similar to the following:

      Sequential Find + Open (Server) = 0.28125
      Sequential Find + Open (Client) = 5.28125
   						

   NOTE: The resulting numbers may differ from computer to computer.