Dynaset-Type Recordset Object

A dynaset-type Recordset object is a dynamic set of records that can contain fields from one or more tables or queries in a database and may be updatable. In an ODBCDirect database, a dynaset-type Recordset object corresponds to an ODBC keyset cursor.

Remarks

A dynaset-type Recordset object is a type of Recordset object you can use to manipulate data in an underlying database table or tables.

It differs from a snapshot-type Recordset object because the dynaset stores only the primary key for each record, instead of actual data. As a result, a dynaset is updated with changes made to the source data, while the snapshot is not. Like the table-type Recordset object, a dynaset retrieves the full record only when it's needed for editing or display purposes.

To create a dynaset-type Recordset object, use the OpenRecordset method on an open database, against another dynaset- or snapshot-type Recordset object, on a QueryDef object, or on a TableDef object. (Opening Recordset objects on other Recordset objects or TableDef objects is available only in Microsoft Jet workspaces.)

If you request a dynaset-type Recordset object and the Microsoft Jet database engine can't gain read/write access to the records, the Microsoft Jet database engine may create a read-only, dynaset-type Recordset object.

As users update data, the base tables reflects these changes. Therefore, current data is available to your application when you reposition the current record. In a multiuser database, more than one user can open a dynaset-type Recordset object referring to the same records. Because a dynaset-type Recordset object is dynamic, when one user changes a record, other users have immediate access to the changed data. However, if one user adds a record, other users won’t see the new record until they use the Requery method on the Recordset object. If a user deletes a record, other users are notified when they try to access it.

Records added to the database don't become a part of your dynaset-type Recordset object unless you add them by using the AddNew and Update methods. For example, if you use an action query containing an INSERT INTO SQL statement to add records, the new records aren't included in your dynaset-type Recordset object until you either use the Requery method or you rebuild your Recordset object using the OpenRecordset method.

To maintain data integrity, the Microsoft Jet database engine can lock dynaset- and table-type Recordset objects during Edit (pessimistic locking) or Update operations (optimistic locking) so that only one user can update a particular record at a time. When the Microsoft Jet database engine locks a record, it locks the entire 2K page containing the record.

You can also use optimistic and pessimistic locking with non-ODBC tables. When you access external tables using ODBC through a Microsoft Jet workspace, you should always use optimistic locking. The LockEdits property and the lockedits parameter of the OpenRecordset method determine the locking conditions during editing.

Not all fields can be updated in all dynaset-type Recordset objects. To determine whether you can update a particular field, check the DataUpdatable property setting of the Field object.

A dynaset-type Recordset object may not be updatable if:

• There isn't a unique index on the ODBC or Paradox table or tables.
  
  
• The data page is locked by another user.
  
  
• The record has changed since you last read it.
  
  
• The user doesn't have permission.
  
  
• One or more of the tables or fields are read-only.
  
  
• The database is opened as read-only.
  
  
• The Recordset object was either created from multiple tables without a JOIN statement or the query was too complex.

The order of a dynaset-type Recordset object or Recordset data doesn't necessarily follow any specific sequence. If you need to order your data, use an SQL statement with an ORDER BY clause to create the Recordset object. You can also use a WHERE clause to filter the records so that only certain records are added to the Recordset object. Using SQL statements in this way to select a subset of records and order them usually results in faster access to your data than using the Filter and Sort properties.