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

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
					

CreateJetDB
					

?CursorLocationTimed()