Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

DAO: Creating A Record Set

After you’ve finished defining a database table with a DAO TableDef object, you can append that object to a Database object, which adds that table to that database. After you’ve installed the new table, you can use the OpenRecordset method to open a record set and start working with data:

Set recordset = Database.OpenRecordset (source, type, options, lockedits)

Here are the arguments for OpenRecordset:

•  source—A string specifying the source of the records for the new Recordset object. The source can be a table name, a query name, or an SQL statement that returns records. (For table-type Recordset objects in Jet-type databases, the source can only be a table name.)

•  type—Indicates the type of Recordset to open.

•  options—Combination of constants that specify characteristics of the new Recordset.

•  lockedits—Constant that determines the locking for Recordset.

Here are the possible settings for type:

•  dbOpenTable—Opens a table-type Recordset object.

•  dbOpenDynamic—Opens a dynamic-type Recordset object, which is like an ODBC dynamic cursor.

•  dbOpenDynaset—Opens a dynaset-type Recordset object, which is like an ODBC keyset cursor.

•  dbOpenSnapshot—Opens a snapshot-type Recordset object, which is like an ODBC static cursor.

•  dbOpenForwardOnly—Opens a forward-only-type Recordset object (where you can only use MoveNext to move through the database).

Here are the possible settings for options:

•  dbAppendOnly—Allows users to append new records to the Recordset but prevents them from editing or deleting existing records (Microsoft Jet dynaset-type Recordset only).

•  dbSQLPassThrough—Passes an SQL statement to a Microsoft Jet-connected ODBC data source for processing (Jet snapshot-type Recordset only).

•  dbSeeChanges—Generates a runtime error if one user is changing data that another user is editing (Jet dynaset-type Recordset only).

•  dbDenyWrite—Prevents other users from modifying or adding records (Jet Recordset objects only).

•  dbDenyRead—Prevents other users from reading data in a table (Jet table-type Recordset only).

•  dbForwardOnly—Creates a forward-only Recordset (Jet snapshot-type Recordset only). It is provided only for backward compatibility, and you should use the dbOpenForwardOnly constant in the type argument instead of using this option.

•  dbReadOnly—Prevents users from making changes to the Recordset (Microsoft Jet only). The dbReadOnly constant in the lockedits argument replaces this option, which is provided only for backward compatibility.

•  dbRunAsync—Runs an asynchronous query (ODBCDirect workspaces only).

•  dbExecDirect—Runs a query by skipping SQLPrepare and directly calling SQLExecDirect (ODBCDirect workspaces only).

•  dbInconsistent—Allows inconsistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).

•  dbConsistent—Allows only consistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).

Here are the possible settings for the lockedits argument:

•  dbReadOnly—Prevents users from making changes to the Recordset (default for ODBCDirect workspaces).

•  dbPessimistic—Uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment.

•  dbOptimistic—Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment.

•  dbOptimisticValue—Uses optimistic concurrency based on row values (ODBCDirect workspaces only).

•  dbOptimisticBatch—Enables batch optimistic updating (ODBCDirect workspaces only).

Let’s see an example to make this clearer. In the previous few topics, we’ve developed a TableDef object, td, in our DAO code example, the daocode project. To append that object to the Database object we created, db, we use the Append method of the database object’s TableDefs collection. After installing the table, we open it for use with the Database object’s OpenRecordset method this way, creating a new DAO Recordset, which we name dbrecordset:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
    td.fields.Append fields(0)
    td.fields.Append fields(1)

    Set dbindex = td.CreateIndex(TableForm.Text2.Text + "index")
    Set IxFlds = dbindex.CreateField(TableForm.Text2.Text)
    dbindex.fields.Append IxFlds
    td.Indexes.Append dbindex
    db.TableDefs.Append td

    Set dbrecordset = db.OpenRecordset(TableForm.Text1.Text, dbOpenTable)
End Sub

In this case, we’re opening the new record set as a standard DAO table by passing the constant dbOpenTable. We also declare dbrecordset as a form-wide variable:

Dim dbrecordset As Recordset

At this point in the daocode project, then, we’ve created a new database with a table in it that has two fields, using the names that the user supplied for the fields and the table itself. And we’ve opened that table as a record set, so we’re ready to work with it and add data to it, which we’ll do in later topics in this chapter.

Besides creating a new database as we’ve done, however, the user may want to open an existing database, and we’ll see how to do that in the next topic.