Dynaset-Type Recordset Object

Description

A dynaset-type Recordset object is a type of Recordset object you can use to manipulate data in an underlying database table or tables. 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.

Remarks

To create a dynaset-type Recordset object, use the OpenRecordset method on an open database, against another dynaset- or snapshot-type Recordset object, or on a QueryDef object.

A dynaset-type Recordset object is different from a snapshot-type Recordset object because only a unique key for each record is brought into memory, instead of actual data. As a result, a dynaset is normally updated with changes made to the source data, while the snapshot is not. Like the table-type Recordset object, a dynaset's current record is fetched only when its fields are referenced.

If you request a dynaset-type Recordset object and the Microsoft Jet database engine is unable to gain read-write access to the records, the Jet database engine may create a read-only, dynaset-type Recordset object. If the Recordset object created isn't updatable, its Updatable property setting is False (0).

As base table data changes due to updates made by your application or by other users, 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 aren't notified 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 using the AddNew method. 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, dynaset- and table-type Recordset objects are locked during Edit (pessimistic locking) and Update methods operations (optimistic locking) so that only one user can update a particular record at a time. When the Jet database engine locks a record, it locks the entire 2K page containing the record.

Optimistic and pessimistic locking are also used with non-ODBC tables. When you access external tables using ODBC you should always use optimistic locking. The locking conditions in effect during editing are determined by the LockEdits property.

Not all fields can be updated in all dynaset-type Recordset objects. To determine whether you can update a particular field, check the Updatable 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 set to 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 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 this technique to filter the records so that only certain records are added to the Recordset object. For example, the following code selects only titles that were published between 1993 and 1994 and sorts the resulting records by title.

Dim dbsBiblio As Database, rstTitles As Recordset
Dim strSelect As String
Set dbsBiblio = Workspaces(0).OpenDatabase("Biblio.mdb")
strSelect = "SELECT * FROM Titles  " _
    & " WHERE [Year Published] BETWEEN 1993 AND 1994 " _
        & " ORDER BY ISBN;"
Set rstTitles = dbsBiblio.OpenRecordset(strSelect, dbOpenDynaset)

Using this technique instead of using the Filter or Sort properties or testing each record individually generally results in faster access to your data.

Properties

AbsolutePosition Property; BOF, EOF Properties; Bookmark Property; Bookmarkable Property; CacheSize, CacheStart Properties; EditMode Property; Filter Property; LastModified Property; LockEdits Property; Name Property; NoMatch Property; PercentPosition Property; RecordCount Property; Restartable Property; Sort Property; Transactions Property; Type Property; ValidationRule Property; ValidationText Property.

Methods

CancelUpdate Method; Clone Method; Close Method; CopyQueryDef Method; Edit Method; FillCache Method; FindFirst, FindLast, FindNext, FindPrevious Methods; GetRows Method; Move Method; MoveFirst, MoveLast, MoveNext, MovePrevious Methods; OpenRecordset Method; Requery Method; Update Method.

See Also

OpenRecordset Method; Recordset Object; Updatable Property; Appendix, "Data Access Object Hierarchy."

Example

This example creates a new dynaset-type Recordset object and opens it, appending it to the Recordsets collection in the default database. It then edits the record(s).

Function ChangeSQL () As Integer
    Dim dbsBiblio As Database, rstTitles As Recordset
    Dim strSelect As String
    Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
    strSelect = "Select * From Titles Where Title = 'Using SQL' " 
    Set rstTitles = dbsBiblio.OpenRecordset(strSelect, dbOpenDynaset)
    If rstTitles.RecordCount > 0 and rstTitles.Updatable Then
        Do Until rstTitles.EOF
            With rstTitles
                .Edit
                ![Year Published] = 1994    ' Change year published.
                .Update
                .MoveNext
            End With
        Loop
    Else
        Debug.Print "No such title or table not updatable"
    End If
    dbsBiblio.Close
    ChangeSQL = True
End Function

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 Database object pointing to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-95#;"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Debug.Print rst.Updatable
End Sub