Recordset Object, Recordsets Collection 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