ASCII table

Different Types of Recordset

In VBA there is not one, but five, different types of Recordset object that you can use. Which one you use depends on a combination of factors, such as: 

The 5 types of Recordset

1. Table

2. Dynaset

3. Snapshot

4. Forward-only

5. Dynamic

You open all five types the same way.

 rec = db.OpenRecordset("TableName",recordset constant)

The OpenRecordset method requires the objectname first, followed with the recordset type constant. If a constant is not provided, then a Table type recordset is made by default.

Creating Recordsets

There are three things you need to consider when creating a recordset object.

1.      Which database are the records in?

2.      Whereabouts in that database are those records?

3.      What type of recordset object do you want?

Table-type Recordset objects

This is the default type for any recordset objects opened against local or attached Access tables. In other words, if we try to create a recordset object against an Access table and we do not specify the type, Access will create a table-type recordset object.

One of the main advantages of using a table-type recordset object is that you can use indexes on the table to speed up the process of searching for specific records. By contrast, you cannot use indexes against the other recordset objects.

Dynaset-type recordset objects

A dynaset-type recordset object can contain either a local or attached table, or the result of a query. It contains a dynamic set of records that is not stored anywhere in the database. There are two key features of dynaset-type recordset objects:

You should use dynaset-type recordset objects when:

SnapShot-type recordset objects

In contrast, snapshot-type recordset objects are not updateable and do not reflect the changes that other users make to the records.  One of the advantages is that with modestly-sized (500 records) recordset object, snapshots are faster that dynasets.

Refreshing Data in Recordsets

If you want to make sure that the data in your recordset is up to date, you can refresh it by executing the requery method against the recordset object.

 For example:

rec.Requery

 This re-executes the query on which the recordset object is based, thus ensuring that the data is up to date.

Using the debug window to view data

So far, we have only really looked at how to create Recordset objects and ensure that the data in them is up to date. But, of course, what we normally want to do is to look at the data itself. 

To refer to individual fields within a recordset object, you can use a variety of different methods. We’ll create a subprocedure which opens a dynaset-type recordset object based on the tblCountries table.

 Example:

1.      In the Whisky.mdb and , create a new module, insert a procedure called OpenWhiskyRecordset. And type the following code into it.

Public Sub OpenWhiskyRecordset()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("Whisky")

Stop

rec.Close
End Sub

4.      Now run the procedure by pressing <F5> or clicking the “run” button. Everything executes up to the Stop command, and it should be highlighted.

5.      We can now open the debug window to inspect records in the recordset. Press Ctrl+G and the debug window appears.

6.   In the pane in the lower half of the window, type:

 ? rec(0), rec(1), rec(2)

 6.      Press Enter. The value of the first three fields for the first record in the Whisky table should be displayed.

3      Brora      Brora, Sutherland

7.      Finally, press <F5> to continue past the Stop command.

How it works

Whenever you create a recordset object, the first row of the recordset becomes the current record. As we created a table-type recordset object, the records are ordered according the Primary key (WhiskID) and so the current row is the record containing data for the whisky called Brora. We can then examine the value of any of the fields in this record.

Moving Through Recordsets

So far, however, things have been rather static. You are able to open a recordset object and inspect all the values in the current record. To move around the records you must use following Move methods:

Example

1.      Run the procedure OpenWhiskyRecordset again.

2.      In the debug window type:

rec.MoveNext

? rec(0), rec(1), rec(2)

3.      Now you get the data from the next record in the record set

4.      Now type:

?rec(“WhiskyName”)

5.      This prints just the data for this record but from the field with the field name “WhiskyName”

6.      Save this module as Example 2.