ADO RecordSet Process
Search/process the fetched data
Access
GetRows
The GetRows method is used to copy records from a Recordset object into a two-dimensional array. The array is automatically dimensioned (sized) to fit the requested number of columns and rows. To allow backwards compatibility with earlier versions of ADO, the columns are placed in the first dimension of the array and the rows are placed in the second dimension.
In comparison, the similar GetString method returns a specified Recordset as a string.
Syntax:
array = recordsetobject.GetRows(Rows, Start, Fields)
There are three optional parameters.
The optional Rows parameter is one of the GetRowsOptionEnum constants which specify how many records to retrieve. Even if you request more records than are available in the Recordset, only the actual number of records will be returned and no error will be generated. The default is to select all records starting from the current record.
Possible values for the GetRowsOptionEnum enumeration are:
| Constant | Value | Description |
|---|---|---|
| adGetRowsRest | -1 | (Default) Retrieves all records from the designated starting point to the end of the records |
The optional Start parameter is a variant that is either a bookmark or one of the BookmarkEnum constants that indicates the starting position for the search. The default is to start at the current record.
BookmarkEnum Constants: Please see the Find method for this enumeration.
The optional Fields parameter is a variant that can be a single field name, an ordinal position of a field, an array of field names, or an array of ordinal positions of the fields that you wish retrieved. It is used to restrict the fields that will be returned by calling this method. The order in which the field names are listed dictates the order in which they are returned.
GetString
The GetString method returns a specified Recordset object as a string.
Syntax:
string = recordsetobject.GetString (StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
In comparison, the similar GetRows method returns a variant that is a two-dimensional array containing selected records from a Recordset object.
There are five optional parameters.
The optional StringFormat parameter is one of the StringFormatEnum constants that define the format to be used when converting the Recordset object to a string.
Possible values for the StringFormatEnum enumeration are:
| Constant | Value | Description |
|---|---|---|
| adClipString | 2 | (Default) Delimits rows by RowDelimiter, columns by ColumnDelimiter, and null values by NullExpr |
The optional NumRows parameter is a long value that specifies how many records in the Recordset to convert to a string. If left blank, the default is to do all of the records. If NumRows exceeds the actual number of available records, only the actual number will be returned and no error will be generated.
The optional ColumnDelimiter parameter is a delimiter character used to space columns for ease of viewing and appearance. The default is the TAB character.
The optional RowDelimiter parameter is a delimiter character used to space rows for ease of viewing and appearance. The default is the CARRIAGE RETURN character.
The optional NullExpr parameter is an expression to use in place of Null. The default is the empty string "".
Save
Saves the Recordset to a file or Stream object.
Delete
Deletes the current record, a group of records, or all records.
Search
Find
The Find method is used to search a Recordset for a Record that matches the search criteria (a search string). This method will work if the Recordset supports bookmarks. If the search is successful, the current record pointer will be moved to point to the first Record that matches. If the search fails, the Recordset will point to either EOF or BOF.
Syntax:
recordsetobject.Find(Criteria, SkipRecords, SearchDirection, Start)
There is one mandatory and three optional parameters.
The mandatory Criteria parameter is a string that defines the search criteria. This string must contain one field (column) name, one comparison operator, and a search value.
You can only search on one field (column).
The comparison operators in Criteria can only be one of the following: = > >= < <= <> LIKE
You cannot use OR or AND.
The value in Criteria can be a date, number, or string. If the value is a string, it must be enclosed (delimited) within a pair of single quotes ("State = ' Tennessee' ") or a pair of pound signs ("State = #Tennessee# "). If the value is a date, it must be enclosed (delimited) within a pair of pound signs ("Birthdate = #6/26/1943# "). Numbers are not delimited ("Age = 104").
If you are using the LIKE operator, you can also use the asterisk * wildcard either after the value in Criteria or before and after the value in Criteria ( "LastName LIKE ' * stein * ' " or "State = ' T * ' ). Some providers also support using the % and _ wildcards.
The optional SkipRecords parameter is a long value that specifies how many records beyond the current record to skip to before starting the search. The default is zero which means that the search starts at the current record.
The optional SearchDirection parameter is one of the SearchDirectionEnum constants that specify which direction the search should proceed, either forward or backward. If no matching record is found for a forward search, the record pointer is set at EOF. If no matching record is found for a backward search, the record pointer is set at BOF.
Possible values for the SearchDirectionEnum enumeration are:
| Constant | Value | Description |
|---|---|---|
| adSearchBackward | -1 | Searches from the designated starting point backward to the first record |
| adSearchForward | 1 | Searches from the designated starting point forward to the last record |
The optional Start parameter is a variant that is either a bookmark or one of the BookmarkEnum constants that indicates the starting position for the search. The default is to start at the current record.
Possible values for the BookmarkEnum enumeration are:
| Constant | Value | Description |
|---|---|---|
| adBookmarkCurrent | 0 | (Default) Start search at current record |
| adBookmarkFirst | 1 | Start search at first record |
| adBookmarkLast | 2 | Start search at last record |
Move
MoveFirst method
Moves the position of the current record pointer to the first record.
MoveLast method
Moves the position of the current record pointer to the last record.
MoveNext method
Moves the position of the current record pointer forward to the next record.
If you are at the last record, calling this method will put you at EOF and the EOF property will be set to True. If you are at EOF and call this method, an error will be generated.
MovePrevious method
Moves the position of the current record pointer back to the previous record.
If you are at the first record, calling this method will put you at BOF and the BOF property will be set to True. If you are at BOF and call this method, an error will be generated.
Sort
The Sort property sets or returns a string value that provides the names of the fields in the Recordset that you wish sorted. Each name must be separated by a delimiter comma and the entire string must be enclosed within a pair of double quotes. If the field name contains blank spaces, you need to enclose it within a pair of square brackets.
You also have the option of specifying that the sort be in ascending or descending order for each individual field. You can declare the sort order by placing a blank space followed by either the keyword ASC, for an ascending sort, or DESC, for a descending sort, directly after the field name, but before the delimiter comma. The default is to sort in ascending order.
The CursorLocation property will need to be set to adUseClient.