Recordset Object

Description

A Recordset object represents the records in a base table or the records that result from running a query.

Remarks

You use Recordset objects to manipulate data in a database at the record level. When you use DAO objects, you manipulate data almost entirely using Recordset objects. All Recordset objects are constructed using records (rows) and fields (columns). There are five types of Recordset objects:

Table-type Recordset — representation in code of a base table that you can use to add, change, or delete records from a single database table (Microsoft Jet workspaces only).
Dynaset-type Recordset — the result of a query that can have updatable records. A dynaset-type Recordset object is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset-type Recordset object can contain fields from one or more tables in a database. This type corresponds to an ODBC keyset cursor.
Snapshot-type Recordset — a static copy of a set of records that you can use to find data or generate reports. A snapshot-type Recordset object can contain fields from one or more tables in a database but can't be updated. This type corresponds to an ODBC static cursor.
Forward-only-type Recordset — identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a result set. This type corresponds to an ODBC forward-only cursor.
Dynamic-type Recordset — a query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records other users add, delete, or edit in the base tables also appear in your Recordset. This type corresponds to an ODBC dynamic cursor (ODBCDirect workspaces only).

You can choose the type of Recordset object you want to create using the type argument of the OpenRecordset method.

In a Microsoft Jet workspace, if you don't specify a type, DAO attempts to create the type of Recordset with the most functionality available, starting with table. If this type isn't available, DAO attempts a dynaset, then a snapshot, and finally a forward-only-type Recordset object.

In an ODBCDirect workspace, if you don't specify a type, DAO attempts to create the type of Recordset with the fastest query response, starting with forward-only. If this type isn't available, DAO attempts a snapshot, then a dynaset, and finally a dynamic-type Recordset object.

When creating a Recordset object using a non-linked TableDef object in a Microsoft Jet workspace, table-type Recordset objects are created. Only dynaset-type or snapshot-type Recordset objects can be created with linked tables or tables in Microsoft Jet-connected ODBC databases.

A new Recordset object is automatically added to the Recordsets collection when you open the object, and is automatically removed when you close it.

Note If you use variables to represent a Recordset object and the Database object that contains the Recordset, make sure the variables have the same scope, or lifetime. For example, if you declare a public variable that represents a Recordset object, make sure the variable that represents the Database containing the Recordset is also public, or is declared in a Sub or Function procedure using the Static keyword.

You can create as many Recordset object variables as needed. Different Recordset objects can access the same tables, queries, and fields without conflicting.

Dynaset-, snapshot-, and forward-only-type Recordset objects are stored in local memory. If there isn't enough space in local memory to store the data, the Microsoft Jet database engine saves the additional data to TEMP disk space. If this space is exhausted, a trappable error occurs.

The default collection of a Recordset object is the Fields collection, and the default property of a Field object is the Value property. Use these defaults to simplify your code.

When you create a Recordset object, the current record is positioned to the first record if there are any records. If there are no records, the RecordCount property setting is 0, and the BOF and EOF property settings are True.

You can use the MoveNext, MovePrevious, MoveFirst, and MoveLast methods to reposition the current record. Forward-only-type Recordset objects support only the MoveNext method. When using the Move methods to visit each record (or "walk" through the Recordset), you can use the BOF and EOF properties to check for the beginning or end of the Recordset object.

With dynaset- and snapshot-type Recordset objects in a Microsoft Jet workspace, you can also use the Find methods, such as FindFirst, to locate a specific record based on criteria. If the record isn't found, the NoMatch property is set to True. For table-type Recordset objects, you can scan records using the Seek method.

The Type property indicates the type of Recordset object created, and the Updatable property indicates whether you can change the object's records.

Information about the structure of a base table, such as the names and data types of each Field object and any Index objects, is stored in a TableDef object.

To refer to a Recordset object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Recordsets(0)

Recordsets("name")

Recordsets![name]

Note You can open a Recordset object from the same data source or database more than once, creating duplicate names in the Recordsets collection. You should assign Recordset objects to object variables and refer to them by variable name.

Properties

AbsolutePosition property, BatchCollisionCount property, BatchCollisions property, BatchSize property, BOF, EOF properties, Bookmark property, Bookmarkable property, CacheSize property, CacheStart property, Connection property, DateCreated, LastUpdated properties, EditMode property, Filter property, Index 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, Seek method, Update method.

See Also Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only-Type Recordset object, Index object, Snapshot-Type Recordset object, Table-Type Recordset object.

Specifics (Microsoft Access)

If you used the . (dot) operator syntax when referring to Recordset objects in applications created in versions 1.x or 2.0 of Microsoft Access, you must modify those references to use the ! (exclamation point) operator syntax. Or, if you want to continue using the . (dot) operator syntax, you must establish a reference to the Microsoft DAO 2.5/3.5 compatibility library in the References dialog box, available by clicking References on the Tools menu while in module Design view.

Example

This example demonstrates Recordset objects and the Recordsets collection by opening four different types of Recordsets, enumerating the Recordsets collection of the current Database, and enumerating the Properties collection of each Recordset.

Sub RecordsetX()

    Dim dbsNorthwind As Database
    Dim rstTable As Recordset
    Dim rstDynaset As Recordset
    Dim rstSnapshot As Recordset
    Dim rstForwardOnly As Recordset
    Dim rstLoop As Recordset
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind

        ' Open one of each type of Recordset object.
        Set rstTable = .OpenRecordset("Categories", _
            dbOpenTable)
        Set rstDynaset = .OpenRecordset("Employees", _
            dbOpenDynaset)
        Set rstSnapshot = .OpenRecordset("Shippers", _
            dbOpenSnapshot)
        Set rstForwardOnly = .OpenRecordset _
            ("Employees", dbOpenForwardOnly)

        Debug.Print "Recordsets in Recordsets " & _
            "collection of dbsNorthwind"

        ' Enumerate Recordsets collection.
        For Each rstLoop In .Recordsets

            With rstLoop
                Debug.Print "    " & .Name

                ' Enumerate Properties collection of each
                ' Recordset object. Trap for any
                ' properties whose values are invalid in
                ' this context.
                For Each prpLoop In .Properties
                    On Error Resume Next
                    If prpLoop <> "" Then Debug.Print _
                        "        " & prpLoop.Name & _
                        " = " & prpLoop
                    On Error GoTo 0
                Next prpLoop

            End With

        Next rstLoop

        rstTable.Close
        rstDynaset.Close
        rstSnapshot.Close
        rstForwardOnly.Close

        .Close
    End With

End Sub

Example (Microsoft Access)

The following example opens a table-type Recordset object, a dynaset-type Recordset object, and a snapshot-type Recordset object. Then it displays the value of the Updatable property for the Recordset objects.

Sub NewRecordsets()
    Dim dbs As Database, rst As Recordset
    Dim rstEmployees As Recordset, rstOrders As Recordset
    Dim rstProducts As Recordset, strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create table-type Recordset object.
    Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Employees", dbOpenDynaset)
    ' Create snapshot-type Recordset object.
    Set rstProducts = dbs.OpenRecordset("Products", dbOpenSnapshot)
    ' Print value of Updatable property for each Recordset object.
    For Each rst In dbs.Recordsets
        Debug.Print rst.Name; "   "; rst.Updatable
    Next rst
    ' Free all object variables.
    rstEmployees.Close
    rstOrders.Close
    rstProducts.Close
    Set dbs = Nothing
End Sub