The Seek Method

The Seek method is the fastest way to find a specific record, but it can only be used on table-type recordsets, because it specifically relies on the table's indexes. Naturally, the table must have at least one index for it to search on. Trying to call Seek against a non-table-type recordset will earn you a runtime error.

Syntax: rst.Seek comparison, key1, key2. . .key13

To use Seek, you must specify three things: the name of the index to use (you can only specify one index at a time), a comparison operator string (which can be <, <=, =, =>, or >), and one or more values that correspond to the value of the key you're looking for. You can specify up to 13 different key values.

For example, the following code shows how to search the tblCustomers table to find a customer whose CustomerNo is 123:

Set

rst =

dbs.OpenRecordset("tblCustomer",

dbOpenTable)

rst.

Index

= "CustomerNo"

rst.

Seek "

'=", 123

You might recall from the section on creating table indexes that the primary key index is called PrimaryKey by default, but you can name it anything you like. So if you want to use the table's primary key index, you must know its name.

To use Seek effectively, you need to understand how it works. If you specify =, =>, or > as the comparison operator, Access starts its search at the beginning of the recordset and works its way forward to the end. If you use any of the other operators, Access starts at the end of the recordset, and moves toward the beginning. With that knowledge, you can see that using Seek within a loop is essentially pointless.

You must specify a key value for each column in the index, particularly if you're using the = comparison operator. The reason is that some of the key fields may default to Null, and since nothing can "equal" Null, your Seek method will usually not find what you're looking for.

The Seek method is not supported for any recordset type in ODBCDirect workspaces, and you can't use Seek on linked tables. But all is not lost, the following code demonstrates how to use Seek on a linked table:

'Open the database that contains the table that is linked

Set dbs = OpenDatabase(strMyExternalDatabase)

'Open a table-type recordset against the external table

Set rst = dbs.OpenRecordset("tblCustomers", dbOpenTable)

'Specify which index to search on rst.Index = "CustomerNo"

'Specify the criteria rst.Seek "=", 123

'Check the result

If rst.NoMatch Then

MsgBox "Record not found."

Else

MsgBox "Customer name: " & rs.CustName End If

What this does (if you haven't already figured out) is open the external database that contains the table that is linked in the current database. It then creates a table-type recordset on the table in that database. In that way, you are operating directly on the table you want to search. The code then searches the table, and finally, checks to see if the search failed. You should never assume that the search is successful; instead, always use the recordset's NoMatch property to determine the result.

Even doing things this way, in most circumstances, the Seek method is still faster thanthe Find methods.

Was this article helpful?

0 0

Responses

  • Fethawi
    What recordset types can the seek method be used in vba?
    8 years ago
  • arthur
    How to perform a seek on an external database in access 2003?
    8 years ago

Post a comment