Find Method (ADO)
The Find method on a Recordset object locates or seeks to the next record in a Recordset object that meets a particular condition and makes this the current record. This method can be used to seek to a specific record in a Recordset object based on a where clause (similar to an SQL where clause) defined by the user.
recordset.Find Criteria, SkipRecords, SearchDirection, Start
ActiveX® Data Objects (ADO) supports four arguments for the Find method, but the last three arguments are optional and have default values as noted below:
- This BSTR parameter specifies the criteria used for locating or seeking to a record in a Recordset object.
- This optional parameter specifies a Long expression that indicates the number of records to skip (whether to skip the current record) when locating a record in a Recordset object. The default value for this argument is 0 (do not skip the current record). The first time a Find method is used, this argument is usually set to 0 (the default). On subsequent calls to this method to seek other records that meet the specified condition, this argument would normally be set to 1, to skip one record forward before finding the next record that matches the search Criteria. A negative value for this parameter is not supported by the Microsoft® OLE DB Provider for AS/400 and VSAM.
- This optional parameter is an enumeration that specifies the direction for the search.
It can be one of the enumerated values for SearchDirectionEnum listed in the following table.
Enumeration Value Description adSearchForward 0 Search forward from the current record. adSearchBackward 1 Search backward from the current record. This option is not supported by the OLE DB Provider for AS/400 and VSAM.
This optional argument defaults to adSearchForward.
- This optional parameter specifies the starting location for a search, which can be a bookmark or an enumeration indicating the current, first, or last record in a Recordset object.
This argument is a Variant and can be either a bookmark or one of the enumerated values for BookmarkEnum listed in the following table.
Enumeration Value Description adBookmarkCurrent 0 The current record. adBookmarkFirst 1 The first record. adBookmarkLast 2 The last record.
This optional argument defaults to adBookmarkCurrent.
The Find method is not supported by the Microsoft OLE DB Provider for DB2 or the Microsoft ODBC Driver for DB2.
The Find method is supported by the Microsoft OLE DB Provider for AS/400 and VSAM on certain files. In order to use the Recordset Find method, an AS/400 logical file, an AS/400 keyed physical file, a mainframe KSDS file with a unique key, or a mainframe RRDS file with a unique key must be used. If this method is used on an AS/400 non-keyed physical file or any other mainframe file type, then the method fails.
The first parameter is the only required argument for the Find method. All of the other arguments are optional and have default values. This first argument is a single-condition where clause. The construction of a single-condition where clause consists of a column name (the database field), an operator (greater than or equal, for example), and a literal value.
Examples of acceptable single-condition where clauses are as follows:
- recordset.Find, "Cost > 10000.00"
- recordset.Find, "Cost < 100.00"
- recordset.Find, "Cost = 5000.00"
- recordset.Find, "LastName = 'Jones' "
Note that variables cannot be used as substitutes for literal values. If the file has multiple keys in the index, using the "=" operator will always fail since the values of all keys cannot be specified.
If the CursorLocation Property is set to adUseClient (use the client cursor engine), the Filter method will work if MDAC 2.0 or later is installed, but will not work properly with earlier versions of ADO.
When operating on large VSAM files and only querying data on a subset of the records, using the Filter property is not desirable because of the performance impact. The entire VSAM file is transferred to the client for filtering. A better solution is to use the server cursor engine and the Find method.To download updated Host Integration Server 2004 Help from www.microsoft.com, go to http://go.microsoft.com/fwlink/?linkid=29507. Copyright © 2004 Microsoft Corporation.
All rights reserved.