The Recordset Object and the Recordsets Collection

The Recordset object represents the records that result from running a query on a Connection object or a Database object in an ODBCDirect workspace. The Recordsets collection contains all currently open Recordset objects on a Connection object or a Database object. The following table shows the relationship between the Recordset object and the Recordsets collection and other objects and collections in an ODBCDirect workspace.

Object or collection Is contained by Contains
Recordset object Recordsets collection Field objects
Properties collection
Recordsets collection Connection object Recordset objects
Database object

The types of Recordset objects supported in an ODBCDirect workspace include the dynaset-type, snapshot-type, forward-only-type, and dynamic-type Recordset objects. For more information on all of these Recordset objects except the dynamic-type Recordset object, see “Dynaset-Type Recordsets,” “Snapshot-Type Recordsets,” “Forward-Only-Type Recordsets” earlier in this chapter. The following section describes dynamic-type Recordset objects.

Note When you use an ODBCDirect workspace, you can’t open a Recordset object using a QueryDef object name as you can from a Microsoft Jet workspace. For example:

Set rst = cnt.OpenRecordset("qrySelectEmps")

Instead, you should perform the OpenRecordset method directly from a QueryDef object like this:

Set qry = cnt.CreateQueryDef("qrySelectEmps", "SELECT * FROM Employees")
Set rst = qry.OpenRecordset()

Or you can specify the SQL statement as the source argument of the OpenRecordset method, as follows:

Set rst = qry.OpenRecordset("SELECT * FROM Employees", dbOpenDynamic)

This is because QueryDef objects in ODBCDirect are not permanent objects as they are in a Microsoft Jet workspace.