Learn DAO VBA

This page provides an organized entry to the MS VBA documentation. Each heading can be clicked to go the to page with that name. The paragraph below it gives summary information what can be found there. It is assumed you have some practical acquaintance with programming in general.

Also see:

Create a DAO Recordset from a table in the current database

The following code example uses the OpenRecordset method to create a table-type Recordset object for a table in the current database.

Create a DAO Recordset from a form

You can create a Recordset object based on an Access form. To do so, use the RecordsetClone property of the form. This creates a dynaset-type Recordset that refers to the same underlying query or data as the form.

Create a DAO Recordset from a query

You can create a Recordset object based on a stored select query. In the following code example, Current Product List is an existing select query stored in the current database.

Add a record to a DAO Recordset

You can add a new record to a table-type or dynaset-type Recordset object by using the AddNew method.

Move through a DAO Recordset

A Recordset object usually has a current position, most often at a record. When you refer to the fields in a Recordset, you obtain values from the record at the current position, which is known as the current record. However, the current position can also be immediately before the first record in a Recordset or immediately after the last record. In certain circumstances, the current position is undefined.

Find a record in a dynaset-type or snapshot-type DAO Recordset

You can use the Find methods to locate a record in a dynaset-type or snapshot-type Recordset object.

Find a record in a table-type DAO Recordset

You use the Seek method to locate a record in a table-type Recordset object. When you use the Seek method to locate a record, the Access database engine uses the table's current index, as defined by the Index property.

Modify an existing record in a DAO Recordset

You can modify existing records in a table-type or dynaset-type Recordset object by using the Edit and Update methods.

Read from and write to a field in a DAO Recordset

When you read or write data to a field, you are actually reading or setting the DAO Value property of a Field object. The DAO Value property is the default property of a Field object. Therefore, you can set the DAO Value property of the LastName field in the rstEmployees Recordset in any of the following ways.

Count the number of records in a DAO Recordset

You may want to know the number of records in a Recordset object. For example, you may want to create a form that shows how many records are in each of the tables in a database. Or you may want to change the appearance of a form or report based on the number of records it includes.

Delete a record from a DAO Recordset

You can delete an existing record in a table or dynaset-type Recordset object by using the Delete method. You cannot delete records from a snapshot-type Recordset object.

Synchronize a DAO Recordset's record with a form's current record

The following code example uses the RecordsetClone property and the Recordset object to synchronize a recordset's record with the form's current record.

Detect the limits of a DAO Recordset

In a Recordset object, if you try to move beyond the beginning or ending record, a run-time error occurs. For example, if you try to use the MoveNext method when you are already at the last record of the Recordset, a trappable error occurs. For this reason, it is helpful to know the limits of the Recordset object.

Extract data from a record in a DAO Recordset

After you have located a particular record or records, you may want to extract data to use in your application instead of modifying the underlying source table.

Find the current position in a DAO Recordset

In some situations, you need to determine how far through a Recordset object you have moved the current record position, and perhaps indicate the current record position to a user. For example, you may want to indicate the current position on a dial, meter, or similar type of control. Two properties are available to indicate the current position: the AbsolutePosition property and the PercentPosition property.

Mark a position in a DAO Recordset

A bookmark is a system-generated Byte array that uniquely identifies each record. The DAO Bookmark property of a Recordset object changes each time you move to a new record. To identify a record, move to that record and then assign the value of the DAO Bookmark property to a variable of type Variant. To return to the record, set the DAO Bookmark property to the value of the variable.

Make bulk changes to a DAO Recordset

After you have created a table-type or dynaset-type Recordset object, you can change, delete, or add new records. You cannot change, delete, or add records to a snapshot-type or forward-only-type Recordset object.

Sort data in a DAO Recordset

Unless you open a table-type Recordset object and set its Index property, you cannot be sure that records will appear in any specific order. However, you usually want to retrieve records in a specific order. For example, you may want to view invoices arranged by increasing invoice number, or retrieve employee records in alphabetical order by their last names. To see records in a specific order, sort them.

Use transactions in a DAO Recordset

A transaction is a set of operations bundled together and handled as a single unit of work. The work in a transaction must be completed as a whole; if any part of the transaction fails, the entire transaction fails. Transactions offer the developer the ability to enforce data integrity. With multiple database operations bundled into a single unit that must succeed or fail as a whole, the database cannot reach an inconsistent state. Transactions are common to most database management systems.

Work with attachments in DAO

In DAO, Attachment fields function just like other multi-valued fields. The field that contains the attachment contains a recordset that is a child to the table's recordset. There are two new DAO methods, LoadFromFile and SaveToFile, that deal exclusively with attachments.

Manipulate multivalued fields with DAO

Multivalued fields are represented as Recordset objects in DAO. The recordset for a field is a child of the recordset for the table that contains the multivalued field. To instantiate the child recordset, use the Value property of the multivalued field as follows.