Dynaset-Type Recordset Object

Description

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.

Properties

AbsolutePosition property, BatchCollisionCount property, BatchCollisions property, BatchSize property, BOF, EOF properties, Bookmark property, Bookmarkable property, CacheSize property, CacheStart property, Connection property, EditMode property, Filter property, LastModified property, LockEdits property, Name property, NoMatch property, PercentPosition property, RecordCount property, RecordStatus property, Restartable property, Sort property, StillExecuting property, Transactions property, Type property, Updatable property, UpdateOptions property, ValidationRule property, ValidationText property.

Methods

AddNew method, Cancel method, CancelUpdate method, Clone method, Close method, CopyQueryDef method, Delete method, Edit method, FillCache method, FindFirst, FindLast, FindNext, FindPrevious methods, GetRows method, Move method, MoveFirst, MoveLast, MoveNext, MovePrevious methods, NextRecordset method, OpenRecordset method, Requery method, Update method.

See Also Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Example

This example opens a dynaset-type Recordset and shows the extent to which its fields are updatable.

Sub dbOpenDynasetX()

    Dim dbsNorthwind As Database
    Dim rstInvoices As Recordset
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstInvoices = _
        dbsNorthwind.OpenRecordset("Invoices", dbOpenDynaset)

    With rstInvoices
        Debug.Print "Dynaset-type recordset: " & .Name

        If .Updatable Then
            Debug.Print "    Updatable fields:"

            ' Enumerate Fields collection of dynaset-type
            ' Recordset object, print only updatable
            ' fields.
            For Each fldLoop In .Fields
                If fldLoop.DataUpdatable Then
                    Debug.Print "        " & fldLoop.Name
                End If
            Next fldLoop

        End If
        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a dynaset-type Recordset object, then checks the Updatable property of the Recordset object:

Sub RecentHires()
    Dim dbs As Database, rst As Recordset
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open recordset on Employees table.
    Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
    Debug.Print rst.Updatable
    rst.Close
    Set dbs = Nothing
End Sub