Access Database Design and Programming, Second Edition

Opening a Recordset

To create, or open, a recordset, Jet provides the OpenRecordset method. This method can be used on Database, TableDef, QueryDef, or existing Recordset objects. The syntax is:

Set RecSetVar = DatabaseVar.OpenRecordset _
(source[, type[, options]])

or:

Set RecSetVar = ObjectVar.OpenRecordset _
([type[, options]])

where:

• ObjectVar points to an existing TableDef, QueryDef, or Recordset object.

• When opening a recordset based upon a database (the first syntax), source is a string specifying the source of the records for the new recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects, the source can only be a table name.

• If you do not specify a type, then a table-type recordset is created if possible. Otherwise, the Type value can be one of the following integer constants:

  • dbOpenTable to open a table-type Recordset object

  • dbOpenDynaset to open a dynaset-type Recordset object

  • dbOpenSnapshot to open a snapshot-type Recordset object

• Options has several possible values related to multiuser situations. It also can take the value dbForwardOnly, which means that the recordset is a forward-only scrolling snapshot. This type of snapshot is useful for rapid searching.

Note

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

The code in Example 16.1 opens, and then closes, a recordset of ...