Microsoft KB Archive/199304

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

Article ID: 199304

Article Last Modified on 8/6/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q199304



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



For a Microsoft Access 2002 and Office Access 2003 version of this article, see 283874.



SUMMARY
This article demonstrates how to find records in a Microsoft Jet database using ActiveX Data Objects (ADO) and OLE DB.



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:  Create a new Microsoft Access database. Create a new module. 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

  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   To create the sample database, type the following line in the Immediate window, and then press ENTER: CreateJetDB   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: <pre class="fixed_text">  Sequential Find + Open (Server) = 0.28125 Sequential Find + Open (Client) = 5.28125 NOTE: The resulting numbers may differ from computer to computer. </li></ol>

<div class="references_section">