Microsoft KB Archive/283874

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

Article ID: 283874

Article Last Modified on 12/21/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q283874



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

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file. 

For a Access 2000 version of this article, see 199304.



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



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 how to use 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 or later version

Microsoft ADO Ext. 2.1 for DDL and Security or later version

  Type or paste 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. 'Change &quot;findseek.mdb&quot; to &quot;findseek.accdb&quot; for Access 2007. 'Change the Provider to &quot;Microsoft.ACE.OLEDB.12.0&quot; for 'Access 2007 ACCDB databases. Kill &quot;c:\findseek.mdb&quot; On Error GoTo 0 ' Create a new Jet 4.0 database name findseek.mdb 'Change &quot;findseek.mdb&quot; to &quot;findseek.accdb&quot; for Access 2007. 'Change the Provider to &quot;Microsoft.ACE.OLEDB.12.0&quot; for 'Access 2007 ACCDB databases. cat.Create &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=c:\findseek.mdb&quot; ' Set the provider, open the database, 'and create a new table called tblSequential. 'Change &quot;findseek.mdb&quot; to &quot;findseek.accdb&quot; for Access 2007. 'Change the Provider to &quot;Microsoft.ACE.OLEDB.12.0&quot; for 'Access 2007 ACCDB databases. cn.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; cn.Open &quot;Data Source=c:\findseek.mdb&quot; cn.Execute &quot;CREATE TABLE tblSequential (col1 long, col2 text(75));&quot; 'Open the new table. rs.Open &quot;tblSequential&quot;, cn, adOpenDynamic, _ adLockOptimistic, adCmdTableDirect ' Add sample records to the tblSequential table. For i = 0 To numrecords rs.AddNew rs.Fields(&quot;col1&quot;).Value = i     rs.Fields(&quot;col2&quot;).Value = &quot;value_&quot; & i      rs.Update Next i  rs.Close 'Create a multifield Index on col1 and col2. cn.Execute &quot;CREATE INDEX idxSeqInt on tblSequential (col1, col2);&quot; '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

'Change &quot;findseek.mdb&quot; to &quot;findseek.accdb&quot; for Access 2007. 'Change the Provider to &quot;Microsoft.ACE.OLEDB.12.0&quot; for 'Access 2007 ACCDB databases. cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _ & &quot;Data Source=c:\findseek.mdb&quot; ' 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 &quot;tblSequential&quot;, cn, adOpenDynamic, adLockOptimistic, _ adCmdTableDirect For i = 0 To 1000 rs.Find &quot;col1=&quot; & i  Next i   Debug.Print &quot;Sequential Find + Open (Server) = &quot; & Timer - time rs.Close ' Time opening a recordset and doing 1000 finds (Client cursor  ' engine) rs.CursorLocation = adUseClient time = Timer rs.Open &quot;tblSequential&quot;, cn, adOpenDynamic, _ adLockOptimistic, adCmdTableDirect For i = 0 To 1000 rs.Find &quot;col1=&quot; & i  Next i   Debug.Print &quot;Sequential Find + Open (Client) = &quot; & Timer - time rs.Close Exit Sub

ErrHandler:

For j = 0 To cn.Errors.Count - 1 Debug.Print &quot;Conn Err Num : &quot;; cn.Errors(j).Number Debug.Print &quot;Conn Err Desc: &quot;; 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 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">