Access Cookbook

Quickly Find a Record in a Linked Table

Problem

You like to use the ultra-fast Seek method to search for data in indexed fields in your table-type recordsets, but the Seek method won’t work with linked tables because you can only open dynaset-type DAO recordsets against linked tables. You can use the Find methods to search for data in these types of recordsets, but Find is much slower at finding data than Seek. Is there any way to use the Seek method on linked tables?

Solution

The Seek method works only on table-type recordsets, so you can’t perform seeks on linked tables. However, there’s no reason why you can’t open the source database that contains the linked table and perform the seek operation there. This solution shows you how to do this.

To use the Seek method on external tables, follow these steps:

1. Use the OpenDatabase method to open the source database that contains the linked table. For example, in the event procedure attached to the cmdSeek command button on the sample form, frmSeekExternal, you’ll find the following code:

   Set wrk = DBEngine.Workspaces(0)
   
   ' Directly open the external database. It will be opened
   ' nonexclusively, read-write, and with type = Access.
   Set dbExternal = _
     wrk.OpenDatabase(acbGetLinkPath("tblCustomer"),, False, False, "")

2. Create a table-type recordset based on the source table. If you renamed the table when you linked to it, make sure you use the name used in the source database. The sample form uses this code:

   ' Create a table-type recordset ...