Table-Type Recordset Object

Description

A table-type Recordset object represents 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 determines the order of the records in the Recordset object (Microsoft Jet workspaces only).

Remarks

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

You can create a table-type Recordset object from a base table of a Microsoft Jet database, but not from an ODBC or linked table. You can use the table-type Recordset object with ISAM databases (like FoxPro, dBASE, or Paradox) when you open them directly.

Unlike dynaset- or snapshot-type Recordset objects, the table-type Recordset object can't refer to more than one base table, and you can't create it with 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 data won't necessarily be in a particular order. 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.

Also unlike dynaset- or snapshot-type Recordset objects, you don't need to explicitly populate table-type Recordset objects to obtain an accurate value for the RecordCount property.

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 LockEdits property determines the locking conditions in effect during editing.

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

AddNew 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 Dynaset-Type Recordset object, Forward-Only-Type Recordset object, Index object, Recordset object, Snapshot-Type Recordset object.

Example

This example opens a table-type Recordset, sets its Index property, and enumerates its records.

Sub dbOpenTableX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' dbOpenTable is default.
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        Debug.Print "Table-type recordset: " & .Name

        ' Use predefined index.
        .Index = "LastName"
        Debug.Print "    Index = " & .Index

        ' Enumerate records.
        Do While Not .EOF
            Debug.Print "        " & !LastName & ", " & _
                !FirstName
            .MoveNext
        Loop

        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example opens a table-type Recordset object, then finds a specified record:

Sub FindEmployee()
    Dim dbs As Database, tdf As TableDef
    Dim rst As Recordset, idx As Index
    Dim fldLastName As Field, fldFirstName As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create new index.
    Set idx = tdf.CreateIndex("FullName")
    ' Create and append index fields.
    Set fldLastName = idx.CreateField("LastName", dbText)
    Set fldFirstName = idx.CreateField("FirstName", dbText)
    idx.Fields.Append fldLastName
    idx.Fields.Append fldFirstName
    ' Append Index object.
    tdf.Indexes.Append idx
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees")
    ' Set current index to new index.
    rst.Index = idx.Name
    ' Specify record to find.
    rst.Seek "=", "Fuller", "Andrew"
    If rst.NoMatch Then
        Debug.Print "Seek failed."
    Else
        Debug.Print "Seek successful."
    End If
    rst.close
    Set dbs = Nothing
End Sub