How to work with recordset (Dao) in MS Access

Recordsets are objects that represent collections (sets) of records. Recordsets have many methods and properties to make working with the records in the collection easy. This page summarizes how to create and use DAO recordsets. Visit the Recordset Builder page to see how this tool included in the Code VBA add-in inserts complete blocks of code for reading and writing records in your MS Access database.

Open a recordset

There are several ways to create or get a recordset:

• Create a new Recordset from a table or query in your database
• Use the Recordset property of an Access object, such as a bound Form
• Clone an existing recordset
• Create a new Recordset by applying a Filter on an existing recordset

In the following sections these different approaches are shown. A small screencast will be included to show where to click to effortlessly get the code inserted.

Create a recordset from a table or query in the current database

The code below opens a recordset taken from a table in the current database

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(Name:="Categories", Type:=RecordsetTypeEnum.dbOpenDynaset)

Click this to see how the code is created with just a few menu selections

In the screencast you see two actions taking place:

1. Insert the OpenRecordset method from the DAO Database Object
2. Insert the Name of a Table

Note: Code VBA automatically understands DAO OpenRecordset uses CurrentDb as the database.

Note: When you use OpenRecordset on a query or attached table Access defaults the Type property to dbOpenDynaset. When you OpenRecordset on a local table, it uses dbOpenTable by default. The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. Consequently if you later, as good practice, split your database the tables become attached tables and the code is likely to fail. For this reason it is safer to just generally stick to dbOpenDynaset.

Add new record to DAO Recordset

To add a new record a recordset has to be be available. The adding of a record requires:

1. AddNew: Start inserting a record
2. Set the values of the fields of the record being created
3. Update: Finalize the adding

Dim rstCategories As Recordset
Set rstCategories = CurrentDb.OpenRecordset(Name:="Categories", Type:=RecordsetTypeEnum.dbOpenDynaset)
With rstCategories
    .AddNew
    ![Category Name] = "Better software"
    !Description = "5 star rated"
    .Update
End With

Notes

1. If a fieldname contains a space you must put it between square brackets.
2. If you add a record to a recordset of type dynaset, the new record will appears at the end of the Recordset, regardless how the Recordset is sorted. To force the new record to appear in its properly sorted position, you can use the Requery method.

Read values from record

To read the field values from a record you first have to make it the current. Subsequently the value of a field can either be obtained using the .Fields method or shorter equivalents

With rstCategories
    lng = !CategoryID
    str1 = ![Category Name]
    str2 = .Fields("Description")
End With

Edit a record in a DAO Recordset

To edit a record in a recordset it first has to be made the current record. After that, changing the values of fields of a record requires:

1. Edit: Indicate the current record is to be edited
2. Set the values of the fields of the record being created
3. Update: Finalize the adding

With rstCategories
    .Edit
    ![Category Name] = "Best software"
    .Update
End With

Check out this to see how the code is created with just a few menu selections

Move through a DAO Recordset - make record current

Moving through a recordset changes what is the 'current' record.

Find a record

The most direct way to move to a specific record is using the FindFirst method.

With rstCategories
    .FindFirst "CategoryName = " & "'better software'"
    If .NoMatch Then

    End If
End With

For best performance, the criteria should be in either the form "field = value" where field is an indexed field in the underlying base table, or "field LIKE prefix" where field is an indexed field in the underlying base table and prefix is a prefix search string (for example, "ART*" ).

After having found the record you can read or change the record's field values as explained under Edit a record in a DAO Recordset.

Processing all records

Use the Move methods to move from record to record without applying a condition. When you open a Recordset, the first record is current. Using any of the Move methods (MoveFirst, MoveLast, MoveNext, or MovePrevious) causes an error if the recordset has no records, so you should test this condition before using a Move method. If, as usually is the case, you use the MoveNext in a loop as below this test is done with .EOF.

Do While Not rst.EOF

    rst.MoveNext
Loop

Delete a record

If you want to delete a record you first have to move to it (see above) making it the current. After that simply

rstCategories.Delete

When you use the Delete method, the Microsoft Access database engine immediately deletes the current record without any warning or prompting. Deleting a record does not automatically cause the next record to become the current record; to move to the next record you must use the MoveNext method.