Understanding the ADODB.Recordset Object

Understanding the ADODB.Recordset Object

Creating, viewing, and updating Recordset objects is the ultimate objective of most Access database front ends. Opening an independent ADODB.Recordset object offers a myriad of cursor, locking, and other options. You must explicitly open a Recordset with a scrollable cursor if you want to use code to create the Recordset for assignment to the Form.Recordset property. Unlike Jet and ODBCDirect Recordset objects, ADODB.Recordset objects expose a number of events that are especially useful for validating Recordset updates.

Recordset Properties

Microsoft attempted to make ADODB.Recordset objects backward compatible with DAO.Recordset objects to minimize the amount of code you must change to migrate existing applications from DAO to ADO. Unfortunately, the attempt at backward compatibility for code-intensive database applications didn't fully succeed. You must make substantial changes in DAO code to accommodate ADO's updated Recordset object. Thus, most Access developers choose ADO for new Access front-end applications and stick with DAO when maintaining existing Jet projects.

Table 30.15 lists the names and descriptions of the standard property set of ADODB.Recordset objects. ADODB.Recordset objects have substantially fewer properties than DAO.Recordset objects have. The standard properties of ADODB.Recordset objects are those that are supported by the most common OLE DB data providers for relational databases.

Table 30.15. Property Names and Descriptions for ADODB.Recordset Objects

Property Name	Description
AbsolutePage	A Long read/write value that sets or returns the number of the page in which the current record is located or one of the constant values of PositionEnum (see Table 30.16). You must set the PageSize property value before getting or setting the value of AbsolutePage. AbsolutePage is 1 based; if the current record is in the first page, AbsolutePage returns 1. Setting the value of AbsolutePage causes the current record to be set to the first record of the specified page.
AbsolutePosition	A Long read/write value (1 based) that sets or returns the position of the current record. The maximum value of AbsolutePosition is the value of the RecordCount property.
ActiveCommand	A Variant read-only value specifying the name of a previously opened Command object with which the Recordset is associated.
ActiveConnection	A pointer to a previously opened Connection object with which the Recordset is associated or a fully qualified ConnectionString value.
BOF	A Boolean read-only value that, when True, indicates that the record pointer is positioned before the first row of the Recordset and there is no current record.
Bookmark	A Variant read/write value that returns a reference to a specific record or uses a Bookmark value to set the record pointer to a specific record.
CacheSize	A Long read/write value that specifies the number of records stored in local (cache) memory. The minimum (default) value is 1. Increasing the value of CacheSize minimizes round trips to the server to obtain additional rows when scrolling through Recordset objects.
CursorLocation	A Long read/write value that specifies the location of a scrollable cursor, subject to the availability of the specified CursorType on the client or server (see Table 30.17). The default is to use a cursor supplied by the OLE DB data source (called a server-side cursor).
CursorType	A Long read/write value that specifies the type of Recordset cursor (see Table 30.18). The default is a forward-only (fire hose) cursor.
DataMember	Returns a pointer to an associated Command object created by Visual Basic's Data Environment Designer.
DataSource	Returns a pointer to an associated Connection object.
EditMode	A Long read-only value that returns the status of editing of the current record (see Table 30.19).
EOF	A Boolean read-only value that, when True, indicates that the record pointer is beyond the last row of the Recordset and there is no current record.
Fields	A pointer to the Fields collection of Field objects of the Recordset.
Filter	A Variant read/write value that can be a criteria string (a valid SQL WHERE clause without the WHERE reserved word), an array of Bookmark values specifying a particular set of records, or a constant value from FilterGroupEnum (see Table 30.20).
Index	A String read/write value that sets or returns the name of an existing index on the base table of the Recordset. The Recordset must be closed to set the Index value to the name of an index. The Index property is used primarily in conjunction with the Recordset.Seek method.
LockType	A Long read/write value that specifies the record-locking method employed when opening the Recordset (see Table 30.21). The default is read-only, corresponding to the read-only characteristic of forward-only cursors.
MarshalOptions	A Long read/write value that specifies which set of records is returned to the server after client-side modification. The MarshallOptions property applies only to the lightweight ADOR.Recordset object, a member of RDS.
MaxRecords	A Long read/write value that specifies the maximum number of records to be returned by a SELECT query or stored procedure. The default value is 0, all records.
PageCount	A Long read-only value that returns the number of pages in a Recordset. You must set the PageSize value to cause PageCount to return a meaningful value. If the Recordset doesn't support the PageCount property, the value is -1.
PageSize	A Long read/writevalue that sets or returns the number of records in a logical page. You use logical pages to break large Recordsets into easily manageable chunks. PageSize isn't related to the size of table pages used for locking in Jet (2KB) or SQL Server (2KB in version 6.5 and earlier, 8KB in version 7+) databases.
PersistFormat	A Long read/write value that sets or returns the format of Recordset files created by calling the Save method. The two constant values of PersistFormatEnum are adPersistADTG (the default format, Advanced Data TableGram or ADTG) and adPersistXML, which saves the Recordset as almost-readable XML. The XML schema, rowset, is a variation of the XML Data Reduced (XDR) schema, a Microsoft-only attribute-centric namespace that isn't compatible with Access's XSD (XML Schema) format.
Properties	A pointer to the Properties collection of provider-specific Property values of the Recordset.
RecordCount	A Long read-only value that returns the number of records in Recordset objects with scrollable cursors if the Recordset supports approximate positioning or Bookmarks. (See the Recordset.Supports method later in this chapter.) If not, you must apply the MoveLast method to obtain an accurate RecordCount value, which retrieves and counts all records. If a forward-only Recordset has one or more records, RecordCount returns -1 (True). An empty Recordset of any type returns 0 (False).
Sort	A String read/write value, consisting of a valid SQL ORDER BY clause without the ORDER BY reserved words, which specifies the sort order of the Recordset.
Source	A String read/write value that can be an SQL statement, a table name, a stored procedure name, or the name of an associated Command object. If you supply the name of a Command object, the Source property returns the value of the Command.CommandText property as text. Use the lngOptions argument of the Open method to specify the type of the value supplied to the Source property.
State	A Long read/write value representing one of the constant values of ObjectStateEnum (refer to Table 30.4).
Status	A Long read-only value that indicates the status of batch operations or other multiple-record (bulk) operations on the Recordset (see Table 30.22).
StayInSync	A Boolean read/write value, which, if set to True, updates references to child (chapter) rows when the parent row changes. StayInSync applies only to hierarchical Recordset objects.

The most obvious omission in the preceding table is the DAO.Recordset's NoMatch property value used to test whether applying one of the DAO.Recordset.Find... methods or the DAO.Recordset.Seek method succeeds. The new ADODB.Recordset.Find method, listed in the "Recordset Methods" section later in this chapter, substitutes for DAO's FindFirst, FindNext, FindPrevious, and FindLast methods. The Find method uses the EOF property value for testing the existence of one or more records matching the Find criteria.

Another omission in the ADODB.Recordset object's preceding property list is the PercentPosition property. The workaround, however, is easy:

 rstName.AbsolutePostion = Int(intPercentPosition * rstName.RecordCount / 100) 

Tables 30.16 through 30.22 enumerate the valid constant values for the AbsolutePage, CursorLocation, CursorType, EditMode, Filter, LockType, and Status properties. Default values appear first, if defined; the list of remaining enumeration members is ordered by frequency of use in Access applications.

Table 30.16. Constant Values for the AbsolutePage Property

AbsolutePageEnum	Description
adPosUnknown	The data provider doesn't support pages, the Recordset is empty, or the data provider can't determine the page number.
adPosBOF	The record pointer is positioned at the beginning of the file. (The BOF property is True.)
adPosEOF	The record pointer is positioned at the end of the file. (The EOF property is True.)

Table 30.17. Constant Values for the CursorLocation Property

CursorLocationEnum	Description
adUseClient	Use cursor(s) provided by a cursor library located on the client. The ADOR.Recordset (RDS) requires a client-side cursor.
adUseServer	Use cursor(s) supplied by the data source, usually (but not necessarily) located on a server (default value).

Table 30.18. Constant Values for the CursorType Property

CursorLocationEnum	Description
adOpenForwardOnly	Provides only unidirectional cursor movement and a read-only Recordset (default value).
adOpenDynamic	Provides a scrollable cursor that displays all changes, including new records, which other users make to the Recordset.
adOpenKeyset	Provides a scrollable cursor that hides only records added or deleted by other users; similar to a DAO.Recordset of the dynaset type.
adOpenStatic	Provides a scrollable cursor over a static copy of the Recordset. Similar to a DAO.Recordset of the snapshot type, but the Recordset is updatable.

Table 30.19. Constant Values for the EditMode Property

EditModeEnum	Description
adEditNone	No editing operation is in progress (default value).
adEditAdd	A tentative append record has been added, but not saved to the database table(s).
adEditDelete	The current record has been deleted.
adEditInProgress	Data in the current record has been modified, but not saved to the database table(s).

Table 30.20. Constant Values for the Filter Property

FilterGroupEnum	Description
adFilterNone	Removes an existing filter and exposes all records of the Recordset (equivalent to setting the Filter property to an empty string, the default value).
adFilterAffectedRecords	View only records affected by the last execution of the CancelBatch, Delete, Resync, or UpdateBatch method.
adFilterFetchedRecords	View only records in the current cache. The number of records is set by the CacheSize property.
adFilterConflictingRecords	View only records that failed to update during the last batch update operation.
adFilterPendingRecords	View only records that have been modified but not yet processed by the data source (for Batch Update mode only).

Table 30.21. Constant Values for the LockType Property

LockTypeEnum	Description
adLockReadOnly	Specifies read-only access (default value).
adLockBatchOptimistic	Use Batch Update mode instead of the default Immediate Update mode.
adLockOptimistic	Use optimistic locking (lock the record or page only during the table update process).
adLockPessimistic	Use pessimistic locking (lock the record or page during editing and the updated process).
adLockUnspecified	No lock type specified. (Use this constant only for Recordset clones.)

Table 30.22. Constant Values for the Status Property (Applies to Batch or Bulk Recordset Operations Only)

RecordStatusEnum	Description of Record Status
adRecOK	Updated successfully
adRecNew	Added successfully
adRecModified	Modified successfully
adRecDeleted	Deleted successfully
adRecUnmodified	Not modified
adRecInvalid	Not saved; the Bookmark property is invalid
adRecMultipleChanges	Not saved; saving would affect other records
adRecPendingChanges	Not saved; the record refers to a pending insert operation)
adRecCanceled	Not saved; the operation was canceled
adRecCantRelease	Not saved; existing record locks prevented saving
adRecConcurrencyViolation	Not saved; an optimistic concurrency locking problem occurred
adRecIntegrityViolation	Not saved; the operation would violate integrity constraints
adRecMaxChangesExceeded	Not saved; an excessive number of pending changes exist
adRecObjectOpen	Not saved; a conflict with an open storage object occurred
adRecOutOfMemory	Not saved; the machine is out of memory
adRecPermissionDenied	Not saved; the user doesn't have required permissions
adRecSchemaViolation	Not saved; the record structure doesn't match the database schema
adRecDBDeleted	Not saved or deleted; the record was previously deleted

Fields Collection and Field Objects

Like DAO's Fields collection, ADO's dependent Fields collection is a property of the Recordset object, making the columns of the Recordset accessible to VBA code and bound controls. The Fields collection has one property, Count, and only two methods, Item and Refresh. You can't append new Field objects to the Fields collection, unless you're creating a persisted Recordset from scratch or you use ADOX's ALTER TABLE DDL command to add a new field.

All but one (Value) of the property values of Field objects are read-only, because the values of the Field properties are derived from the database schema. The Value property is read-only in forward-only Recordsets and Recordsets opened with read-only locking. Table 30.23 gives the names and descriptions of the properties of the Field object.

Table 30.23. Property Names and Descriptions of the Field Object

Field Property	Description
ActualSize	A Long read-only value representing the length of the Field's value by character count.
Attributes	A Long read-only value that represents the sum of the constants (flags) included in FieldAttributeEnum (see Table 30.24).
DefinedSize	A Long read-only value specifying the maximum length of the Field's value by character count.
Name	A String read-only value that returns the field (column) name.
NumericScale	A Byte read-only value specifying the number of decimal places for numeric values.
OriginalValue	A Variant read-only value that represents the Value property of the field before applying the Update method to the Recordset. (The CancelUpdate method uses OriginalValue to replace a changed Value property.)
Precision	A Byte read-only value specifying the total number of digits (including decimal digits) for numeric values.
Properties	A collection of provider-specific Property objects. SQL Server 2000's extended properties are an example Properties collection members for the SQL Server OLE DB provider.
Status	An undocumented Long read-only value.
Type	A Long read-only value specifying the data type of the field. Refer to Table 30.14 for Type constant values.
UnderlyingValue	A Variant read-only value representing the current value of the field in the database table(s). You can compare the values of OriginalValue and UnderlyingValue to determine whether a persistent change has been made to the database, perhaps by another user.
Value	A Variant read/write value of a subtype appropriate to the value of the Type property for the field. If the Recordset isn't updatable, the Value property is read-only.

Value is the default property of the Field object, but it's a good programming practice to set and return field values by explicit use of the Value property name in VBA code. In most cases, using varName = rstName.Fields(n).Value instead of varName = rstName.Fields(n) results in a slight performance improvement.

Table 30.24. Constant Values and Descriptions for the Attributes Property of the Field Object

FieldAttributeEnum	Description
adFldCacheDeferred	The provider caches field values. Multiple reads are made on the cached value, not the database table.
adFieldDefaultStream	The field contains a stream of bytes. For example, the field might contain the HTML stream from a Web page specified by a field whose adFldIsRowURL attribute is True.
adFldFixed	The field contains fixed-length data with the length determined by the data type or field specification.
adFldIsChapter	The field is a member of a chaptered recordset and contains a child recordset of this field.
adFldIsCollection	The field contains a reference to a collection of resources, rather than a single resource.
adFldIsNullable	The field accepts Null values.
adFldIsRowURL	The field contains a URL for a resource such as a Web page.
adFldKeyColumn	The field is the primary key field of a table.
adFldLong	The field has a long binary data type, which permits the use of the AppendChunk and GetChunk methods.
adFldMayBeNull	The field can return Null values.
adFldMayDefer	The field is deferrable, meaning that Values are retrieved from the data source only when explicitly requested.
adFldNegativeScale	The field contains data from a column that supports negative Scale values.
adFldRowID	The field is a row identifier (typically an identity, AutoIncrement, or GUID data type).
adFldRowVersion	The field contains a timestamp or similar value for determining the time of the last update.
adFldUpdatable	The field is read/write (updatable).
adFldUnknownUpdatable	The data provider can't determine whether the field is updatable. Your only recourse is to attempt an update and trap the error that occurs if the field isn't updatable.

The Field object has two methods, AppendChunk and GetChunk, which are applicable only to fields of various long binary data types, indicated by an adFldLong flag in the Attributes property of the field. The AppendChunk method is discussed in the "Parameter Object" section earlier in this chapter. The syntax for the AppendChunk method call, which writes Variant data to a long binary field (fldName), is

 fldName.AppendChunk varData 

Note

ADO 2.x doesn't support the Access OLE Object field data type, which adds a proprietary object wrapper around the data (such as a bitmap) to identify the OLE server that created the object (for bitmaps, usually Windows Paint).

The GetChunk method enables you to read long binary data in blocks of the size you specify. Following is the syntax for the GetChunk method:

 varName = fldName.GetChunk(lngSize) 

A common practice is to place AppendChunk and GetChunk method calls within Do Until... Loop structures to break up the long binary value into chunks of manageable size. In the case of the GetChunk method, if you set the value of lngSize to less than the value of the field's ActualSize property, the first GetChunk call retrieves lngSize bytes. Successive GetChunk calls retrieve lngSize bytes beginning at the next byte after the end of the preceding call. If the remaining number of bytes is less than lngSize, only the remaining bytes appear in varName. After you retrieve the field's bytes, or if the field is empty, GetChunk returns Null.

Note

Changing the position of the record pointer of the field's Recordset resets GetChunk's byte pointer. Accessing a different Recordset and moving its record pointer doesn't affect the other Recordset's GetChunk record pointer.

Recordset Methods

ADODB.Recordset methods are an amalgam of the DAO.Recordset and rdoResultset methods. Table 30.25 gives the names, descriptions, and calling syntax for Recordset methods. OLE DB data providers aren't required to support all the methods of the Recordset object. If you don't know which methods the data provider supports, you must use the Supports method with the appropriate constant from CursorOptionEnum, listed in Table 30.28 later in this chapter, to test for support of methods that are provider dependent. Provider-dependent methods are indicated by an asterisk after the method name in Table 30.25.

Table 30.25. Names and Descriptions of Methods of the Recordset Object

Method Name	Description and Calling Syntax
AddNew	Adds a new record to an updatable Recordset. The calling syntax is rstName. AddNew [{varField|avarFields}, {varValue|avarValues}] where varField is a single field name, avarFields is an array of field names, varValue is single value, and avarValues is an array of values for the columns defined by the members of avarFields. Calling the Update method adds the new record to the database table(s). If you add a new record to a Recordset having a primary-key field that isn't the first field of the Recordset, you must supply the name and value of the primary-key field in the AddNew statement.
Cancel	Cancels execution of an asynchronous query and terminates creation of multiple Recordsets from stored procedures or compound SQL statements. The calling syntax is rstName. Cancel.
CancelBatch	Cancels a pending batch update operation on a Recordset whose LockEdits property value is adBatchOptimistic. The calling syntax is rstName. CancelBatch [lngAffectRecords]. The optional lngAffectRecords argument is one of the constants of AffectEnum (see Table 30.26).
CancelUpdate	Cancels a pending change to the table(s) underlying theRecordset before applying the Update method. The calling syntax is rstName. CancelUpdate.
Clone	Creates a duplicate Recordset object with an independent record pointer. The calling syntax is Set rstDupe = rstName. Clone().
Close	Closes a Recordset object, allowing reuse of the Recordset variable by setting new Recordset property values and applying the Open method. The calling syntax is rstName. Close.
CompareBookmarks	Returns the relative value of two bookmarks in the same Recordset or a Recordset and its clone. The calling syntax is lngResult = rstName. CompareBookmarks(varBookmark1, varBookmark2).
Delete	Deletes the current record immediately from the Recordset and the underlying tables, unless the LockEdits property value of the Recordset is set to adLockBatchOptimistic. The calling syntax is rstName. Delete.
Find	Searches for a record based on criteria you supply. The calling syntax is rstName. Find strCriteria[, lngSkipRecords, lngSearchDirection[, lngStart]], where strCriteria is a valid SQL WHERE clause without the WHERE keyword, the optional lngSkipRecords value is the number of records to skip before applying Find, lngSearchDirection specifies the search direction (adSearchForward, the default, or adSearchBackward), and the optional varStart value specifies the Bookmark value of the record at which to start the search or one of the members of BookmarkEnum (see Table 30.27). If Find succeeds, the EOF property returns False; otherwise, EOF returns True.
GetRows	Returns a two-dimensional (row, column) Variant array of records. The calling syntax is avarName = rstName. GetRows(lngRows[, varStart[, {strFieldName|lngFieldIndex|avarFieldNames| avarFieldIndexes}]]), where lngRows is the number of rows to return, varStart specifies a Bookmark value of the record at which to start the search or one of the members of BookmarkEnum (see Table 30.27), and the third optional argument is the name or index of a single column, or a Variant array of column names or indexes. If you don't specify a value of the third argument, GetRows returns all columns of the Recordset.
GetString	By default, returns a tab-separated String value for a specified number of records, with records separated by return codes. The calling syntax is strClip = rstName. GetString (lngRows[, strColumnDelimiter[, strRowDelimiter, [strNullExpr]]]), where lngRows is the number of rows to return, strColumnDelimiter is an optional column-separation character (vbTab is the default), strRowDelimiter is an optional row-separation character (vbCR is the default), and strNullExpr is an optional value to substitute when enco untering Null values (an empty string, "", is the default value).
Move	Moves the record pointer from the current record. The calling syntax is rstName. Move lngNumRecords[, varStart], where lngNumRecords is the number of records by which to move the record pointer and the optional varStart value specifies the Bookmark of the record at which to start the search or one of the members of BookmarkEnum (see Table 30.27).
MoveFirst	Moves the record pointer to the first record. The calling syntax is rstName. MoveFirst.
MoveLast	Moves the record pointer to the last record. The calling syntax is rstName. MoveLast.
MoveNext	Moves the record pointer to the next record. The calling syntax is rstName. MoveNext. The MoveNext method is the only Move... method that you can apply to a forward-only Recordset.
MovePrevious	Moves the record pointer to the previous record. The calling syntax is rstName. MovePrevious.
NextRecordset	Returns additional Recordset objects generated by a com pound Jet SQL statement, such as SELECT * FROM Orders; SELECT * FROM Customers, or a T-SQL stored procedure that returns multiple Recordsets. The calling syntax is rstNext = rstName. NextRecordset [(lngRecordsAffected)], where lngRecordsAffected is an optional return value that specifies the number of records in rstNext, if SET NOCOUNT ON isn't included in the SQL statement or stored procedure code. If no additional Recordset exists, rstNext is set to Nothing.
Open	Opens a Recordset on an active Command or Connection object. The calling syntax is rstName. Open [varSource[, varActiveConnection[, lngCursorType[, lngLockType[, lngOptions]]]]]. The Open arguments are optional if you set the equivalent Recordset property values, which is the practice recommended in this book. For valid values, refer to the Source, ActiveConnection, CursorType, and LockType properties in Table 30.15 earlier in this chapter and to the CommandTypeEnum values listed in Table 30.7 earlier in this chapter for the lngOptions property.
Requery	Refreshes the content of the Recordset from the underlying table(s), the equivalent of calling Close and then Open. Requery is a very resource-intensive operation. The calling syntax is rstName. Requery.
Resync	Refreshes a specified subset of the Recordset from the underlying table(s). The calling syntax is rstName. Resync [lngAffectRecords], where lngAffectRecords is one of the members of AffectEnum (see Table 30.26). If you select adAffectCurrent or adAffectGroup as the value of lngAffectRecords, you reduce the required resources in comparison with adAffectAll (the default).
Save	Creates a file containing a persistent copy of the Recordset. The calling syntax is rstName. Save strFileName, where strFileName is the path to and the name of the file. You open a Recordset from a file with a rstName. Open strFileName, Options:= adCmdFile statement. This book uses .rst as the extension for persistent Recordsets in the ADTG format and .xml for XML formats.
Seek	Performs a high-speed search on the field whose index name is specified as the value of the Recordset.Index property. The calling syntax is rstName. Seek avarKeyValues[, lngOption], where avarKeyValues is a Variant array of search values for each field of the index. The optional lngOption argument is one of the members of the SeekEnum (see Table 30.29) constant enumeration; the default value is adSeekFirstEQ (find the first equal value). You can't specify adUseClient as the CursorLocation property value when applying the Seek method; Seek requires a server-side (adUseServer) cursor.
Supports	Returns True if the Recordset's data provider supports a specified cursor-dependent method; otherwise, Supports returns False. The calling syntax is blnSupported = rstName. Supports(lngCursorOptions). Table 30.28 lists the names and descriptions of the CursorOptionEnum values.
Update	Applies the result of modifications to the Recordset to the underlying table(s) of the data source. For batch operations, Update applies the modifications only to the local (cached) Recordset. The calling syntax is rstName. Update.
UpdateBatch	Applies the result of all modifications made to a batch-type Recordset (LockType property set to adBatchOptimistic, CursorType property set to adOpenKeyset or adOpenStatic, and CursorLocation property set to adUseClient) to the underlying table(s) of the data source. The calling syntax is rstName. UpdateBatch [lngAffectRecords], where lngAffectRecords is a member of AffectEnum (see Table 30.26).

The "Code to Pass Parameter Values to a Stored Procedure" section, earlier in the chapter, illustrates use of the Save and Open methods with persisted Recordsets of the ADTG type.

Tip

The Edit method of DAO.Recordset objects is missing from Table 30.25. To change the value of one or more fields of the current record of an ADODB.Recordset object, execute rstName. Fields(n).Value = varValue for each field whose value you want to change and then execute rstName.Update.ADODB.Recordset objects don't support the Edit method.

To improve the performance of Recordset objects opened on Connection objects, set the required property values of the Recordset object and then use a named argument to specify the intOptions value of the Open method, as in rstName.Open Options:= adCmdText. This syntax is easier to read and less prone to error than the alternative, rstName. Open , , , , adCmdText.

Table 30.26. Names and Descriptions of Constants for the CancelBatch Method's lngAffectRecords Argument

AffectEnum	Description
adAffectAll	Include all records in the Recordset object, including any records hidden by the Filter property value (the default)
adAffectAllChapters	Include all chapter fields in a chaptered recordset, including any records hidden by the Filter property value.
adAffectCurrent	Include only the current record
adAffectGroup	Include only those records that meet the current Filter criteria

Table 30.27. Names and Descriptions of Bookmark Constants for the Find Method's varStart Argument

BookmarkEnum	Description
adBookmarkCurrent	Start at the current record (the default value)
adBookmarkFirst	Start at the first record
adBookmarkLast	Start at the last record

Table 30.28. Names and Descriptions of Constants for the Supports Method

CursorOptionEnum	Permits
adAddNew	Applying the AddNew method
adApproxPosition	Setting and getting AbsolutePosition and AbsolutePage property values
adBookmark	Setting and getting the Bookmark property value
adDelete	Applying the Delete method
adFind	Applying the Find method
adHoldRecords	Retrieving additional records or changing the retrieval record pointer position without committing pending changes
adIndex	Use of the Index property
adMovePrevious	Applying the GetRows, Move, MoveFirst, and MovePrevious methods (indicates a bidirectional scrollable cursor)
adNotify	Use of Recordset events
adResync	Applying the Resync method
adSeek	Applying the Seek method
adUpdate	Applying the Update method
adUpdateBatch	Applying the UpdateBatch and CancelBatch methods

Table 30.29 lists the SeekEnum constants for the optional lngSeekOptions argument of the Seek method. Unfortunately, the syntax for the ADODB.Recordset.Seek method isn't even close to being backward-compatible with the DAO.Recordset.Seek method.

Table 30.29. Names and Descriptions of Constants for the Seek Method's lngSeekOptions Argument

SeekEnum	Finds
adSeekFirstEQ	The first equal value (the default value)
adSeekAfterEQ	The first equal value or the next record after which a match would have occurred (logical equivalent of >=)
adSeekAfter	The first record after which an equal match would have occurred (logical equivalent of >)
adSeekBeforeEQ	The first equal value or the previous record before which a match would have occurred (logical equivalent of <=)
adSeekBefore	The first record previous to where an equal match would have occurred (logical equivalent of <)
adSeekLastEQ	The last record having an equal value

Tip

Use the Find method for searches unless you are working with a table having an extremely large number of records. Find takes advantage of index(es), if present, but Find's search algorithm isn't quite as efficient as Seek's. You'll probably encounter the threshold for considering substituting Seek for Find in the range of 500,000 to 1,000,000 records. Tests on a large version the Oakmont.mdb Jet and Oakmont SQL Server Students table (50,000) rows show imperceptible performance differences between Seek and Find operations.

Recordset Events

Recordset events are new to users of DAO. Table 30.30 names the Recordset events and gives the condition under which the event fires.

Table 30.30. Names and Occurrence of Recordset Events

Event Name	When Fired
EndOfRecordset	When the record pointer attempts to move beyond the last record
FetchComplete	When all records have been retrieved asynchronously
FetchProgress	During asynchronous retrieval, periodically reports the number of records returned
FieldChangeComplete	After a change to the value of a field
MoveComplete	After execution of the Move or Move... methods
RecordChangeComplete	After an edit to a single record
RecordsetChangeComplete	After cached changes are applied to the underlying tables
WillChangeField	Before a change to a field value
WillChangeRecord	Before an edit to a single record
WillChangeRecordset	Before cached changes are applied to the underlying tables
WillMove	Before execution of the Move or Move... methods