The Seek Method

The ADO Seek method, although a little different from its DAO cousin, is still the fastest way to find a specific record, but it can only be used with server-side cursors on tables that have been opened as adCmdTableDirect, because it specifically relies on the table's indexes (and the indexes reside on the server—not on the client). Naturally, the table must have at least one index for it to search on.

Syntax: rs.Seek KeyValues, SeekOption

To use the ADO Seek method, you must specify three things: the name of the index key to use (although an index can be made up of multiple columns, you can only specify one index at a time), a variant array whose members specify the values to be compared with the key columns, and a SeekEnum constant that defines the kind of Seek to execute. The SeekOption constant can be one of the following.

Constant

Value

Description

AdSeekFirstEQ

1

Locates the first key that is equal to the value specified in KeyValues.

AdSeekLastEQ

2

Locates the last key that is equal to the value specified in KeyValues.

AdSeekAfterEQ

4

Locates the key that is equal to the value specified in KeyValues, or the key just after it.

AdSeekAfter

8

Locates a key that is just after where a match with a value specified in KeyValues would have occurred.

AdSeekBeforeEQ

16

Locates the key that is equal to the value specified in KeyValues, or the key just after where it would have occurred.

AdSeekBefore

32

Locates a key that is just before where a match with a value specified in KeyValues would have occurred.

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

Set rs = db.OpenRecordset("tblCustomer",

. dbOpenTable)

rs.Index = "CustomerNo"

rs.Seek 123, adSeekFirstEQ

If rs.EOF Then

'A matching record was found

Else

'A matching record was not found

End If

Primary key indexes in Jet databases are called PrimaryKey, whereas primary key indexes in the SQL Server are called PK_tablename by default, but you can name them anything you like. So if you want to use the table's primary key index, you must know its name.

You must specify a key value for each column in the index. 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.

In contrast with the DAO Seek method where you would check the NoMatch property to see if the search succeeded or failed, the ADO Seek method has no such property. If the method finds a record that matches the criteria, the Recordset object's cursor is moved to that row, if not, to the end of the recordset. So if no matching record is found, the Recordset object's EOF property is set to True.

0 0

Post a comment