ACC2000: How to Use the Seek Method with ActiveX Data Objects (ADO) Against a Jet Recordset

ID: Q243465


The information in this article applies to:
  • Microsoft Access 2000

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

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


SUMMARY

This article demonstrates 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp


MORE INFORMATION

The following example illustrates how to use the Seek method to find a customer order with a particular Order ID and Product ID. If 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 function, you must use a server-side cursor, as denoted in the CursorLocation property in the following code.

Also, you can only use the Seek method 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 only use the Seek method 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 2000 database.


  2. Click Modules under Objects, and then click New.


  3. On the Tools menu, click References. Make sure the following two libraries are included in the Available References: box:


    • Microsoft ActiveX Data Objects 2.1 Library


    • Microsoft ADO Ext. 2.1 for DDL and Security


  4. In the new module, type the following code:


  5. 
    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 = "C:\Program Files\Microsoft " _
                & "Office\Office\Samples\Northwind.mdb"
            .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 
    Note that in the code, the path to Northwind.mdb may vary from computer to computer.
  6. On the Debug menu, click Compile DatabaseName.


  7. In the Immediate window, type the following line, and then press ENTER:


  8. SeekRecord


REFERENCES

For additional information about using the Seek method with Microsoft Jet tables, click the article number below to view the article in the Microsoft Knowledge Base:

Q249683 ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database

Additional query words: inf

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


Last Reviewed: January 17, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.