Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to use the Seek method with ActiveX Data Objects (ADO) against a Jet recordset

View products that this article applies to.


This article shows you how to use the Seek method with an ActiveX Data Objects (ADO) recordset.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

↑ Back to the top

More information

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.
  1. Create a new Microsoft Access database, and name it Db1.mdb.
  2. Click Modules under Objects, and then click New.
  3. 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).
  4. 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>"
        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
    Note that in the code, the path to Northwind.mdb may vary from computer to computer.
  5. On the Debug menu, click Compile Db1.
  6. In the Immediate window, type the following line, and then press ENTER:

↑ Back to the top


For additional information about using the Seek method with Microsoft Jet tables, click the following article number to view the article in the Microsoft Knowledge Base:
290060 ACC2002: Error Setting Index Property of ADO Recordset That Is Based on a Microsoft Jet Database

↑ Back to the top

Keywords: KB287638, kbhowto, kbdta, kbprogramming, kbdatabase, kbado

↑ Back to the top

Article Info
Article ID : 287638
Revision : 11
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 336