Table-Type Recordset Object

Description

A table-type Recordset object is a representation in code of a base table you can use to add, change, or delete records from a table. Only the current record is loaded into memory. A predefined index is used to determine the order of the records in the Recordset object.

Remarks

To create a table-type Recordset object, use the OpenRecordset method on an open Database object.

A table-type Recordset object can be created with a base table of a Microsoft Jet database, but not with an ODBC or attached table. The table-type Recordset object can be used with ISAM databases (like FoxPro, dBASE, Paradox, or Btrieve) when opened directly.

Unlike dynaset- or snapshot-type Recordset objects, the table-type Recordset object can't refer to more than one base table, and it can't be accessed using an SQL statement that filters or sorts the data. Generally, when you access a table-type Recordset object, you specify one of the predefined indexes for the table, which orders the data returned to your application. If the table doesn't have an index, the order of the data returned can't be guaranteed. If necessary, your application can create an index that returns records in a specific order. To choose a specific order for your table-type Recordset object, set the Index property to a valid index.

To maintain data integrity, table-type Recordset objects are locked during the Edit and Update methods operations 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.

Two kinds of locking are used with non-ODBC tables — pessimistic and optimistic. ODBC-accessed tables always use optimistic locking. The locking conditions in effect during editing are determined by the LockEdits property.

Properties

BOF, EOF Properties; Bookmark Property; Bookmarkable Property; DateCreated, LastUpdated Properties; EditMode Property; Index Property; LastModified Property; LockEdits Property; Name Property; NoMatch Property; PercentPosition Property; RecordCount Property; Restartable Property; Transactions Property; Type Property; Updatable Property; ValidationRule Property; ValidationText Property.

Methods

CancelUpdate Method; Clone Method; Close Method; Delete Method; Edit Method; GetRows Method; Move Method; MoveFirst, MoveLast, MoveNext, MovePrevious Methods; OpenRecordset Method; Seek Method; Update Method.

See Also

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

Example

This example opens a table-type Recordset and selects an index for the Recordset. By setting an index, the Microsoft Jet database engine returns records in the order specified by the index. Without an index, table-type Recordset objects return records from the database table in no particular order.

    Dim dbsExample As Database
    Dim rstTitles as Recordset
    ' Get workspace and database.

    Set dbsExample = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
    Set rstTitles = dbsExample.OpenRecordset("Titles", dbOpenTable)
    rstTitles.Index = "MyIndex"
    ...
    ...
    ...

Example (Microsoft Access)

The following example opens a table-type Recordset object, then prints the number of records in the Recordset object.

Sub CountEmployees()
    Dim dbs As Database, tdf As TableDef, rst As Recordset

    ' Return Database object pointing to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
    rst.MoveLast
    Debug.Print rst.RecordCount
End Sub