|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 283874 - Last Review: December 21, 2007 - Revision: 7.2 How to find a record using ADO and Jet OLE DB providerThis 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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=199304/
)
.
This article demonstrates how to use ActiveX Data Objects
(ADO) and OLE DB to find records in a Microsoft Jet database. 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 "findseek.mdb" to "findseek.accdb" for Access 2007.
'Change the Provider to "Microsoft.ACE.OLEDB.12.0" for
'Access 2007 ACCDB databases.
Kill "c:\findseek.mdb"
On Error GoTo 0
' Create a new Jet 4.0 database name findseek.mdb
'Change "findseek.mdb" to "findseek.accdb" for Access 2007.
'Change the Provider to "Microsoft.ACE.OLEDB.12.0" for
'Access 2007 ACCDB databases.
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.
'Change "findseek.mdb" to "findseek.accdb" for Access 2007.
'Change the Provider to "Microsoft.ACE.OLEDB.12.0" for
'Access 2007 ACCDB databases.
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
'Change "findseek.mdb" to "findseek.accdb" for Access 2007.
'Change the Provider to "Microsoft.ACE.OLEDB.12.0" for
'Access 2007 ACCDB databases.
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:
- To demonstrate the Find method, type the following line in the Immediate window, and then
press ENTER: You should see output similar to the following:
Sequential Find + Open (Server) = 0.28125
Sequential Find + Open (Client) = 5.28125
To learn more about ActiveX Data Objects, visit the
following Microsoft Web site:
APPLIES TO- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbprogramming kbhowto KB283874 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |