ACC2000: How to FIND a Record Using ADO and Jet OLE DB Provider

• Microsoft Access 2000

SUMMARY

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

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

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 on 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 New Catalog
   Dim cn As New Connection
   Dim rs As New Recordset
   Dim numrecords As Long
   Dim i As Long
   
   ' 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 New Connection
   Dim rs As New Recordset
   Dim i, j As Long
   Dim time As Variant
   
   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.

REFERENCES

To learn more about ActiveX Data Objects, visit the following Microsoft Web site:

http://www.microsoft.com/data/ado/

Additional query words: inf

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto