Manipulating Data

You can use DAO to manipulate data in your database. In DAO, you use the Recordset object to interact with data stored in the database. Recordset objects are extremely powerful because they provide a great deal of flexibility in working with data.

Recordset objects are different from most other DAO objects in that they exist only as temporary objects. Even though the method you use to create Recordset objects is called OpenRecordset, you don’t actually open existing Recordset objects; you create new ones. After you close a Recordset object, it no longer exists.

To create a Recordset, you use the OpenRecordset method based on a Database object. The syntax for the OpenRecordset method is:

Set recordset = object.OpenRecordset(source[, type[, options[, lockedits]]])

In this syntax, recordset is an object variable that represents the Recordset object you want to open. Object is an object variable that represents an existing object from which you want to create the new Recordset object. The source argument is the source of the data, such as a table name, a query name, or an SQL statement that returns records. You can open Recordset objects:

Directly on tables in the database.
On tables in other databases.
On tables linked to external data sources such as ODBC or ISAM.
On queries.
Based on SQL strings that you dynamically create in code.
On other Recordset objects.

The type argument is a numeric constant denoting the type of Recordset to open. There are five types of Recordset objects:

Table-type Recordset object. A representation in code of a table in the current database. You can use table-type Recordset objects to retrieve, add, change, or delete records.
Dynaset-type Recordset object. The result of a query that can have updatable records. A dynaset is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset can contain fields from one or more tables in a database.
Snapshot-type Recordset object. A static copy of a set of records that you can use to find data or generate reports. A snapshot can contain fields from one or more tables in a database, but it can’t be updated.
Forward-only-type Recordset object. This type of Recordset object is identical to a snapshot-type Recordset object, except that you can only scroll forward through its records. This improves performance in situations where you only need to make a single pass through a result set.
Note DAO 3.5 introduced the dbOpenForwardOnly constant as a new value for the type argument of the OpenRecordset method. This Recordset object behaves the same as a DAO 3.0 snapshot-type Recordset object opened with the dbForwardOnly option.
Dynamic-type Recordset object. This type of Recordset object represents a query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records that users add, delete, or edit in the base tables also appear in your Recordset object. This Recordset object is similar to an ODBC dynamic cursor and is available in ODBCDirect workspaces only.

You can set the options argument to a combination of constants that specify characteristics of the new Recordset object. For example, with the options argument, you can specify:

That users can’t modify or add records in the table or tables you are accessing.
That users can’t view records (table-type Recordset only).
That users can append new records, but can’t edit or delete existing records (dynaset-type Recordset only).
That inconsistent updates are allowed (dynaset-type Recordset only).
That only consistent updates are allowed (dynaset-type Recordset only).

Note When you don’t use the options argument, you must specify 0 for it.

The lockedits argument determines the locking for the new Recordset object. For example, you can:

Prevent users from making changes to the Recordset object (default setting for ODBCDirect workspaces).
Use pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record you’re editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).
Use optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record you’re editing isn’t locked until the Update method executes.