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. A snapshot-type Recordset object can contain fields from one or more tables in a database but can't be updated.

Remarks

When you create a snapshot-type Recordset object, data values for all fields (except Memo and OLE Object 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. For example, the following code selects only titles whose name contains the string "VBSQL" and sorts the resulting records by ISBN:

Dim dbsBiblio As Database, rstTitles As Recordset
Dim strSelect As String
Set dbsBiblio = Workspaces(0).OpenDatabase("Biblio.mdb")
strSelect = "SELECT * FROM Titles WHERE Title like '*VBSQL*' " & _
    "ORDER BY ISBN;"
Set rstTitles = dbsBiblio.OpenRecordset(strSelect,dbOpenSnapshot)

Using this technique instead of using the Filter or Sort properties or testing each record individually generally results in faster access to your data.

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.

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; BOF, EOF Properties; Bookmark Property; Bookmarkable Property; Filter Property; Name Property; NoMatch Property; PercentPosition Property; RecordCount Property; Restartable Property; Sort Property; Transactions Property; Type Property; Updatable Property; ValidationRule Property; ValidationText Property.

Methods

Clone Method; Close Method; CopyQueryDef Method; FindFirst, FindLast, FindNext, FindPrevious Methods; GetRows Method; Move Method; MoveFirst, MoveLast, MoveNext, MovePrevious Methods; OpenRecordset Method; Requery Method.

See Also

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

Example

This example creates a new snapshot-type Recordset object and opens it, appending it to the Recordsets collection in the default database. It then finds a record and prints it.

Function ShowVBSQL () As Integer
    Dim dbsPublish As Database, rstTitles As Recordset
    Dim I As Integer, J As Integer
    Set dbsPublish = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb") 
    Set rstTitles = dbsPublish.OpenRecordset("Titles", dbOpenSnapshot)
    If rstTitles.RecordCount > 0 Then
        rstTitles.FindFirst "Title Like '*VBSQL*'"  ' Any title on VBSQL
            Do until rstTitles.NoMatch = True
                Debug.Print rstTitles!Title
                rstTitles.FindNext "Title Like '*VBSQL*'"
            Loop
    Else
        Debug.Print "No such title"
    End If
    dbsPublish.Close
    ShowVBSQL = True
End Function

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 Database object pointing to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Employees WHERE HireDate <= #1-1-95#;"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    Debug.Print rst.Updatable
End Sub