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 ObjectsProperty 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). | | 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 PropertyAbsolutePageEnum | 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 PropertyCursorLocationEnum | 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 PropertyCursorLocationEnum | 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 PropertyEditModeEnum | 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 PropertyFilterGroupEnum | 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 PropertyLockTypeEnum | 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 ObjectField 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 ObjectFieldAttributeEnum | 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 ObjectMethod 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. | | 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 ArgumentAffectEnum | 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 ArgumentBookmarkEnum | 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 MethodCursorOptionEnum | 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 ArgumentSeekEnum | 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 EventsEvent 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 |
|