The following example shows you how to use the
Seek method to find a customer order with a particular Order ID and
Product ID. If the order is found, the example prints the quantity of the
customer order in the Immediate window.
Usually, you can choose
between using a client-side cursor library or one that is located on the
server. In order for the
Seek method to work, you must use a server-side cursor, as denoted in
the
CursorLocation property.
Additionally, you can use the
Seek method only when a recordset is accessing the table directly. In
this example, the recordset is instructed to access the table directly by the
adCmdTableDirect argument in the
Open method. You cannot use the
Seek method on objects such as queries and linked tables. You can use
the
Seek method only on native Microsoft Jet tables. If your database
contains linked tables, you can open an external connection to the back-end
database that stores the table, and then use the
Seek method directly on the table.
- Create a new Microsoft Access database, and name it
Db1.mdb.
- Click Modules under Objects, and then click New.
- On the Tools menu, click References. Make sure the Microsoft ActiveX Data Objects 2.x Library is included in the Available References box (where 2.x is version 2.1 or later).
- In the new module, type or paste the following code:
Option Compare Database
Option Explicit
Function SeekRecord()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
'Set the connection properties and open the connection.
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "<Drive>\<Path to Northwind sample database>"
.Open
End With
With rst
'Select the index used in the recordset.
.Index = "PrimaryKey"
'Set the location of the cursor service.
.CursorLocation = adUseServer
'Open the recordset.
.Open "Order Details", conn, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
'Find the customer order where OrderID = 10255 and ProductID = 16.
.Seek Array(10255, 16), adSeekFirstEQ
'If a match is found, print the quantity of the customer order.
If Not rst.EOF Then
Debug.Print rst.Fields("Quantity").Value
End If
End With
End Function
- On the Debug menu, click Compile Db1.
- In the Immediate window, type the following line, and then
press ENTER:
SeekRecord