ADO - ADO Recordset Seek Method

The Seek method in ADO is used to search for a particular record in an ADO Recordset. It performs the search by using an index on the underlying table. Unlike methods that search through records sequentially, Seek can locate records efficiently when the data source supports indexed searching.

1. Purpose of the Seek Method

The main purpose of the Seek method is to find a record based on one or more values of an indexed field.

For example, suppose a database contains an Employees table with the following fields:

  • EmployeeID

  • EmployeeName

  • Department

  • Salary

If EmployeeID is an indexed field, the Seek method can be used to locate the employee whose EmployeeID is 105.

The basic syntax is:

recordset.Seek KeyValues, SeekOption

Here:

  • recordset is the ADO Recordset object.

  • KeyValues contains the value or values being searched for.

  • SeekOption specifies how the search should be performed.

2. How Seek Works

Before using Seek, the Recordset must generally be opened with a cursor type and configuration that supports indexed searching. The Recordset also needs to have an appropriate index available.

A simplified example is:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "Employees", connection, adOpenKeyset, adLockOptimistic, adCmdTable

rs.Index = "PrimaryKey"
rs.Seek Array(105), adSeekFirstEQ

In this example:

  1. The Employees table is opened as a Recordset.

  2. The Index property identifies the index that will be used.

  3. Seek searches the index for the value 105.

  4. adSeekFirstEQ tells ADO to find the first record whose indexed value is equal to 105.

If the record is found, the Recordset's current position moves to that record.

3. The Index Property

The Index property is particularly important when working with Seek.

For example:

rs.Index = "PrimaryKey"

This tells ADO which index should be used for the search.

The selected index must be appropriate for the values supplied to Seek. If a table has several indexes, the application can select the required index before performing the search.

For example:

rs.Index = "EmployeeIDIndex"

The exact indexes available depend on the underlying database provider and data source.

4. Seek Options

The second argument of the Seek method determines how the search value should be compared.

Common Seek options include:

adSeekFirstEQ
adSeekLastEQ
adSeekAfter
adSeekAfterEQ
adSeekBefore
adSeekBeforeEQ

Their meanings are:

adSeekFirstEQ

Searches for the first record whose indexed value is equal to the specified value.

adSeekLastEQ

Searches for the last record whose indexed value is equal to the specified value.

adSeekAfter

Searches for a record whose indexed value occurs after the specified value.

adSeekAfterEQ

Searches for a record whose indexed value is greater than or equal to the specified value.

adSeekBefore

Searches for a record whose indexed value occurs before the specified value.

adSeekBeforeEQ

Searches for a record whose indexed value is less than or equal to the specified value.

The exact behavior can depend on the provider and the type of index being used.

5. Example of Searching for an Employee

Consider an Employees table:

EmployeeID EmployeeName Department
101 Arun Sales
102 Meena HR
103 Ravi IT
104 Priya Finance
105 Kiran IT

Suppose we want to find employee 105.

The code could be structured as:

rs.Index = "PrimaryKey"
rs.Seek Array(105), adSeekFirstEQ

If rs.EOF Then
    MsgBox "Employee not found"
Else
    MsgBox rs.Fields("EmployeeName").Value
End If

If the search succeeds, the current record becomes the record for employee 105, and the employee's name can be retrieved from the Recordset.

6. Searching with Multiple Key Values

The Seek method can also work with indexes containing multiple fields.

For example, suppose an index is based on:

Department + EmployeeID

The search can provide multiple values:

rs.Seek Array("IT", 105), adSeekFirstEQ

Here, ADO searches using both the department and employee ID values.

This is useful when a database uses a composite index.

7. Seek and Recordset Position

When Seek successfully finds a matching record, the Recordset's current record changes to the matching record.

The application can then access the fields:

MsgBox rs.Fields("EmployeeName").Value

It can also inspect other fields:

MsgBox rs.Fields("Department").Value

Thus, Seek is not simply a method that returns a value. It changes the current position of the Recordset to the located record.

8. Checking Whether a Record Was Found

After performing a search, an application should check whether the search was successful.

For example:

rs.Seek Array(999), adSeekFirstEQ

If rs.EOF Then
    MsgBox "No matching employee found."
Else
    MsgBox "Employee found: " & rs.Fields("EmployeeName").Value
End If

If there is no matching record, the application can handle the situation instead of attempting to access nonexistent data.

9. Advantages of the Seek Method

The Seek method can be useful when an application needs to locate records using indexed fields.

Its major advantages include:

  1. It uses an index-based search where supported.

  2. It can be more efficient than manually examining records one by one.

  3. It supports searches using equality and relative comparison options.

  4. It can work with single-field and composite indexes.

  5. It moves the Recordset directly to the located record.

10. Limitations of the Seek Method

The Seek method is not universally supported by every ADO provider or data source.

One important limitation is that the underlying provider must support the required indexed operations. If the provider does not support Seek, another searching technique may be required.

The Recordset also needs to be configured appropriately, and an applicable index must be available.

Therefore, developers should not assume that every ADO Recordset can use Seek simply because it is part of the ADO Recordset object model.

11. Seek Method vs Find Method

Seek and Find are both used to locate records, but they work differently.

Feature Seek Find
Primary mechanism Index-based search Search through Recordset
Requires an index Generally yes No
Provider support More restricted More broadly supported
Search performance Can be efficient with suitable indexes May require sequential searching
Typical use Indexed table searches Searching a Recordset using criteria

For example, Find can be used like:

rs.Find "EmployeeID = 105"

Whereas Seek uses an index and a search option:

rs.Index = "PrimaryKey"
rs.Seek Array(105), adSeekFirstEQ

12. Practical Applications

The Seek method can be useful in applications such as:

  • Employee management systems

  • Inventory applications

  • Banking systems

  • Customer databases

  • Student management systems

  • Order processing systems

  • Billing applications

For example, an inventory application could use an indexed product ID to quickly locate a particular product record before updating its stock quantity.

Conclusion

The ADO Recordset Seek method provides an index-based mechanism for locating records in a Recordset. It works together with the Recordset's Index property and different seek options to locate records based on indexed values. Its main benefit is efficient searching when the underlying provider and Recordset support indexed operations. However, because provider support and Recordset requirements can vary, developers should verify that the selected data source supports Seek before relying on it in an application.