| ADO |  |
| ADO | |
ADO provides the Find and Seek methods for locating a particular record in a Recordset. The Find method is supported by a variety of providers but is limited to a single search criterion. The Seek method supports searching on multiple criteria, but is not supported by many providers.
Indexes on fields can greatly enhance the performance of the Recordset object's Find method and Sort and Filter properties. You can create an internal index for a Field object by setting its dynamic Optimize property. This dynamic property is added to the Field object's Properties collection when you set the CursorLocation property to adUseClient. Remember that this index is internal to ADO—you cannot gain access to it or use it for any other purpose. Also, this index is distinct from the Recordset object's Index property.
The Find method quickly locates a value within a column (field) of a Recordset. You can often improve the speed of the Find method's operation on a column by using the Optimize property to create an index on it.
The Find method limits your search to the contents of one field. The Seek method requires that you have an index and has other limitations as well. If you need to search on multiple fields that aren't the basis of an index, or if your provider does not support indexes, you can limit your results using the Filter property of the Recordset object.
The Find method searches a Recordset for the row that satisfies a specified criterion. Optionally, the direction of the search, starting row, and offset from the starting row may be specified. If the criterion is met, the current row position is set on the found record; otherwise, the position is set to the end (or start) of the Recordset, depending on search direction.
Only a single-column name may be specified for the criterion. In other words, this method does not support multi-column searches.
The comparison operator for the criterion may be ">" (greater than), "<" (less than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal), "<>" (not equal), or "LIKE" (pattern matching).
The criterion value may be a string, floating-point number, or date. String values are delimited with single quotes or "#" (number sign) marks (for example, "state = 'WA'" or "state = #WA#"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#").
If the comparison operator is "like", the string value may contain an asterisk (*) to find one or more occurrences of any character or substring. For example, "state like 'M*'" matches Maine and Massachusetts. You can also use leading and trailing asterisks to find a substring contained within the values. For example, "state like '*as*'" matches Alaska, Arkansas, and Massachusetts.
Asterisks can be used only at the end of a criteria string or together at both the beginning and end of a criteria string, as shown above. You cannot use the asterisk as a leading wildcard ('*str') or embedded wildcard ('s*r'). This will cause an error.
Use the Seek method in conjunction with the Index property if the underlying provider supports indexes on the Recordset object. Use the Supports(adSeek) method to determine whether the underlying provider supports Seek, and the Supports(adIndex) method to determine whether the provider supports indexes. (For example, the OLE DB Provider for Microsoft Jet supports Seek and Index.)
If Seek does not find the desired row, no error occurs, and the row is positioned at the end of the Recordset. Set the Index property to the desired index before executing this method.
This method is supported only with server-side cursors. Seek is not supported when the Recordset object's CursorLocation property value is adUseClient.
This method can be used only when the Recordset object has been opened with a CommandTypeEnum value of adCmdTableDirect.