Creating a Recordset Variable

The OpenRecordset method is the basic method of creating a Recordset object variable. To use it, you first declare a variable of type Recordset object, and then set the variable to the object returned by the OpenRecordset method.

The OpenRecordset method is available from Database, TableDef, QueryDef and existing Recordset objects. For ODBCDirect workspaces, the OpenRecordset method is available from the Connection, Database, and QueryDef objects. The syntax of the OpenRecordset method for Database and Connection objects is:

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

The syntax of the OpenRecordset method for all other types of objects is:

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

The recordset argument is the name of the new Recordset object.

The object argument is the object from which you’re creating the new Recordset object. Microsoft Jet uses the source argument only for Recordset objects created from Database or Connection objects.

The source argument is an existing TableDef or QueryDef object in the database or a valid record-returning SQL query or statement. For TableDef, QueryDef, and Recordset objects, the object itself is automatically used as the source.

The type argument is an intrinsic constant that specifies the kind of Recordset object you want to create. The available type constants are:

• dbOpenTable
  
  
• dbOpenDynamic
  
  
• dbOpenDynaset
  
  
• dbOpenSnapshot
  
  
• dbOpenForwardOnly

If you open a Recordset object in a Microsoft Jet workspace and you don’t specify a type, OpenRecordset creates a table-type Recordset object, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset object. In an ODBCDirect workspace, the default setting is dbOpenForwardOnly.

With the options argument of the OpenRecordset method, you can specify a number of other features for a Recordset object by using the following constants:

• dbAppendOnly With this constant, you can only append new records to the Recordset object; you cannot edit or delete existing records. This is useful in applications that collect and archive data (dynaset only).
  
  
• dbSeeChanges If another user changes data in a record on which this Recordset object has invoked the Edit method, but before it has invoked the Update method, a run-time error will occur. This is useful in applications where multiple users have simultaneous read/write access to the same data (dynaset only).
  
  
• dbDenyWrite When used on a dynaset or snapshot, this constant prevents other users from opening a table or dynaset-type Recordset object, although they can still open snapshot- or forward-only-type Recordset objects (Microsoft Jet workspaces only).
  
  
• dbDenyRead Prevents other users from reading data in the table (table only).
  
  
• dbReadOnly Prevents your Recordset object from modifying data. This is useful where the Recordset object will be used to display data in a form that otherwise allows data entry. The dbReadOnly constant in the lockedits argument replaces this constant, which is provided only for backward compatibility (Microsoft Jet workspaces only).
  
  
• dbForwardOnly Creates a forward-only snapshot. Note that Recordset objects created with this option cannot be cloned and only support the Move and MoveNext methods to move directly through the records (snapshot only). This constant is provided for backward compatibility only. To create a forward-only-type Recordset object, you should use the dbOpenForwardOnly constant in the type argument instead of in this argument.
  
  
• dbSQLPassThrough Where the source argument is an SQL statement, use this constant to pass the SQL statement to an ODBC data source for processing. If used with a dynaset, data is not updatable (Microsoft Jet snapshot only).
  
  
• dbConsistent (Default) Only consistent updates are allowed (Microsoft Jet dynaset and snapshot only).
  
  
• dbInconsistent Inconsistent updates are allowed. Opposite of dbConsistent (Microsoft Jet dynaset and snapshot only).

  See Also For more information about inconsistent updates, see “Inconsistent Updates” later in this chapter.

• dbRunAsync Runs an asynchronous query (ODBCDirect workspaces only).
  
  
• dbExecDirect Runs a query that skips SQLPrepare and directly calls SQLExecDirect. Use this constant only when you’re not opening a Recordset object based on a parameter query (ODBCDirect workspaces only).

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

With the lockedits argument of the OpenRecordset method, you can specify locking options for a Recordset object by using the following constants:

• dbReadOnly Prevents users from making changes to the Recordset object. You can use this constant in either the options argument or the lockedits argument, but not both. If it is used in both arguments, an error occurs. This setting is the default in ODBCDirect workspaces.
  
  
• dbPessimistic Uses pessimistic locking to determine how changes are made to a Recordset object in a multiuser environment. With pessimistic locking, the page that contains the record being edited is locked as soon as the Recordset object’s Edit method is invoked. This setting is the default in Microsoft Jet workspaces.
  
  
• dbOptimistic Uses optimistic locking to determine how changes are made to a Recordset object in a multiuser environment. With optimistic locking, the page that contains the record being edited is not locked until the Recordset object’s Update method is invoked.
  
  
• dbOptimisticValue Uses optimistic concurrency based on record values (ODBCDirect workspaces only).
  
  
• dbOptimisticBatch Enables batch optimistic updating (ODBCDirect workspaces only).

See Also For more information about the dbOptimisticValue and dbOptimisticBatch constants, see Chapter 9, “Developing Client/Server Applications.” For more information about ODBCDirect, see Chapter 8, “Accessing External Data,” and Chapter 9, “Developing Client/Server Applications.”