Snapshot-Type Recordset Object

Description

A snapshot-type Recordset object is a static set of records that you can use to examine data in an underlying table or tables. In an ODBCDirect database, a snapshot-type Recordset object corresponds to a static cursor.

Remarks

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

A snapshot-type Recordset object can contain fields from one or more tables in a database. In a Microsoft Jet workspace, a snapshot can't be updated. In an ODBCDirect workspace, a snapshot may be updatable, depending on the ODBC driver.

When you create a snapshot-type Recordset object, data values for all fields (except Memo and OLE Object (Long Binary) field data types in .mdb files) are brought into memory. Once loaded, changes made to base table data aren't reflected in the snapshot-type Recordset object data. To reload the snapshot-type Recordset object with current data, use the Requery method, or re-execute the OpenRecordset method.

The order of snapshot-type Recordset object data doesn't necessarily follow any specific sequence. 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. Using this technique instead of using the Filter or Sort properties or testing each record individually generally results in faster access to your data.

Snapshot-type Recordset objects are generally faster to create and access than dynaset-type Recordset objects because their records are either in memory or stored in TEMP disk space, and the Microsoft Jet database engine doesn't need to lock pages or handle multiuser issues. However, snapshot-type Recordset objects use more resources than dynaset-type Recordset objects because the entire record is downloaded to local memory.

Properties

AbsolutePosition property, BatchCollisionCount property, BatchCollisions property, BatchSize property, BOF, EOF properties, Bookmark property, Bookmarkable property, Connection property, EditMode property, Filter 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, FindFirst, FindLast, FindNext, FindPrevious methods, GetRows method, Move method, MoveFirst, MoveLast, MoveNext, MovePrevious methods, NextRecordset method, OpenRecordset method, Requery method, Update method.

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

Example

This example opens a snapshot-type Recordset and demonstrates its read-only characteristics.

Sub dbOpenSnapshotX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenSnapshot)

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

        ' Enumerate the Properties collection of the
        ' snapshot-type Recordset object, trapping for
        ' any properties whose values are invalid in
        ' this context.
        For Each prpLoop In .Properties
            On Error Resume Next
            Debug.Print "    " & _
                prpLoop.Name & " = " & prpLoop
            On Error Goto 0
        Next prpLoop

        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a snapshot-type Recordset object from an SQL statement, then prints the value of the Updatable property for the Recordset object. Since snapshot-type Recordset objects are never updatable, the value of this property will always be False (0).

Sub LongTermEmployees()
    Dim dbs As Database, qdf As QueryDef, rst As Recordset
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Construct SQL string.
    strSQL = "SELECT * FROM Employees WHERE HireDate <= #1-1-94#;"
    ' Open snapshot-type Recordset object.
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    Debug.Print rst.Updatable
    rst.Close
    Set dbs = Nothing
End Sub