Recordset Object

Description

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

Remarks

When you use data access objects, you interact with data almost entirely using Recordset objects. All Recordset objects are constructed using records (rows) and fields (columns).

There are three types of Recordset objects:

Table-type Recordset — a representation in code of a base table that you can use to add, change, or delete records from a single database table.
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.
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.

You can choose the type of Recordset object you want to create using the type argument of the OpenRecordset method. If you don't specify a type, the Microsoft Jet database engine attempts to create a table-type Recordset object. If this isn't possible, the Jet database engine attempts a dynaset-type and then a snapshot-type Recordset object.

When creating a Recordset object using a nonattached TableDef object, table-type Recordset objects are created. Only dynaset-type or snapshot-type Recordset objects can be created with attached tables or tables in external ODBC databases.

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

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

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.

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 global variable that represents a Recordset object, make sure the variable that represents the database containing the recordset is also global, or is declared in a Sub or Function procedure using the Static keyword.

Your application can create as many Recordset object variables as needed. A Recordset object can refer to one or more tables or queries, or can be an SQL statement. You can also have a Recordset object that refers to some or all of the fields or records in other Recordset objects.

Dynaset- and snapshot-type Recordset objects are stored in local memory. If there isn't enough space in local memory to store the data, the 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. You can simplify your code by using these defaults. For example, the following lines of code all set the value of the PubID field in the current record of a Recordset object.

rstPublishers!PubID = 99
rstPublishers("PubID") = 99
rstPublishers.Fields("PubID").Value = 99

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. For dynaset- and snapshot-type Recordset objects, 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 function.

To cycle through all records, you can use the BOF and EOF properties to check for the beginning or end of the Recordset object. However, it may run faster to with an SQL query that performs operations that apply to an entire set of records.

Properties

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

Methods

AddNew 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; OpenRecordset Method; Requery Method; Seek Method; Update Method.

See Also

Dynaset-Type Recordset Object; Index Object; OpenRecordset Method; Snapshot-Type Recordset Object; Table-Type Recordset Object; Appendix, "Data Access Object Hierarchy."

Example

This example creates a new Recordset object and opens it (thereby appending it to the Recordsets collection) in the default database. Then the example enumerates all the Recordset objects in the current database and all the fields in each Recordset object and closes the new Recordset.

Function EnumerateRecordset () As Integer
    Dim dbsExample As Database, rstOrders As Recordset
    Dim rstTemp As Recordset
    Dim I As Integer, J As Integer
    Set dbsExample = _ 
        DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb") 
    Set rstOrders = dbsExample.OpenRecordset("Orders", dbOpenSnapshot)
    Debug.Print
    ' Enumerate all Recordset objects. 
    For J = 0 To dbsExample.Recordsets.Count - 1
        Set rstTemp = dbsExample.Recordsets(J)
        Debug.Print
        Debug.Print "Enumeration of Recordset objects("; J; "): "; _ 
            rstTemp.Name
        Debug.Print
        ' Enumerate fields.
        Debug.Print "Fields: Name, Type, Value"
        For I = 0 To rstTemp.Fields.Count - 1
            Debug.Print "  "; rstTemp.Fields(I).Name;
            Debug.Print ", "; rstTemp.Fields(I).Type;
            If rstTemp.Fields(I).Type = dbText Then 
                Debug.Print ", "; rstTemp.Fields(I).Value
            End if
        Next I
    Next J
    dbsExample.Close
    EnumerateRecordset = True
End Function

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 Database object pointing to current database.
    Set dbs = CurrentDb
    ' Create table-type Recordset object.
    Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)
    ' Construct SQL string.
    strSQL = "SELECT * FROM Orders WHERE OrderDate >= #1-1-95#;"
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset(strSQL, 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
End Sub