As you saw in the preceding section, you often need a way to find a specific record when working with recordsets. DAO provides two ways to find a specific record: Seek and Find. The one you choose to use depends entirely on the type of recordset you want to use it on.
The Seek Method
The Seek method is the fastest way to find a specific record, but it can be used only 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. Seek uses the following syntax:
rst.Seek comparison, key1, key2. . .key13
To use Seek, you must specify three things: the name of the index to use (you can specify only 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, although you can name it anything you like. 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 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 because nothing can "equal" Null, your Seek method will usually not find what you're looking for.
The Seek method is not supported for 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 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, so that you are operating directly on the table you want to search. The code searches the table and, finally, checks to see if the search failed. 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 than the Find methods.
The Find Methods
There are four Find methods: FindFirst, FindPrevious, FindNext, and FindLast. Their purpose is self-evident, given their names, and you can use them on all recordset types.
Because the Find methods enable you to specify any field in the criteria, they may not be capable of using a table's indexes to execute a search. Compare this to the Seek method, which always uses a table's indexes to execute the search. Without an indexed field, the Find methods can just as easily use a table scan to find the right record; it just depends on the type of search, and amount of data being searched. Not surprisingly then, using the Find methods is usually far slower than using Seek.
A table scan is where the database engine must read each record as a part of a search. This often results in a query or operation that is significantly slower than methods such as Seek.
The Find methods can be used on filtered dynaset and snapshot recordsets, which minimizes the number of records that have to be searched.
In addition, because you have FindNext and FindPrevious methods at your disposal, you don't have to start at the beginning or end of the recordset to find subsequent matches; you can just keep searching until you find the record you want.
All four methods use the same syntax:
rs.[FindFirst | FindPrevious | FindNext | FindLast] criteria
The criteria argument can be any valid SQL WHERE clause, without the word WHERE. For example, the following code demonstrates how to find all instances of a customer having the word parts in his or her name.
Sub FindOrgName() Dim dbs As DAO.Database Dim rst As DAO.Recordset 'Get the database and recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblCustomers") 'Search for the first matching record rst.FindFirst "[OrgName] LIKE '*parts*'" 'Check the result If rst.NoMatch Then MsgBox "Record not found." GoTo Cleanup Else Do While Not rs.NoMatch MsgBox "Customer name: " & rst!CustName rs.FindNext "[OrgName] LIKE '*parts*'" Loop 'Search for the next matching record rst.FindNext "[OrgName] LIKE '*parts*'" End If Cleanup: rst.Close Set rst = Nothing Set dbs = Nothing End Sub
Once a matching record is found, any subsequent search begins from the current cursor position, not the start or end of the recordset like in the Seek method. Again, always follow the search with a check of the recordset's NoMatch property, to determine the result of the search.
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields