Recordsets Collection (DAO)

Office 2013 and later

Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

A Recordsets collection contains all open Recordset objects in a Connection or Database object.

When you use DAO objects, you manipulate data almost entirely using Recordset objects.

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

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

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 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.

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.

VBA
Copy 
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
Show: