Introduction to Record Sets

After creating a table, you can fill it with records as we have done in the previous lessons. Here is an example:

We also saw in the previous lessons that you could create a query that selects a few records from a table. The records that a table or a query holds are referred to as a set of records or a record set. To consider all of them as an entity, Microsoft Access provides an object called a Recordset. Most of the time, you use a Recordset object without being aware because the database engine handles all or most of the necessary routines behind the scenes. In some other cases, you must create or initiate a Recordset object in order to use it. To do this, you have various alternatives. In fact, the way you create a Recordset object may depend on the library you are using at the time you need to "grab" the set of records.

When creating a record set as we will see in the next few sections, you can specify whether to use one or more tables and what record would be included. How the record set is created, the number of tables, the number of records, and the types of operations that that can be performed on the records lead to various type of record sets: If your Recordset object includes only one table, it is referred to a Table record set If a record set includes one or more tables, it is called a Dynaset. This type allows adding, editing, updating, or deleting the records that are part of the Recordset object A record set is called a Snapshot if it allows you to view the records of one or more tables, navigating back and forth in the set, but you cannot make changes on the records A Recordset object is referred to as Forward-Only if you can view its records without changing them but you can only move forward. This means that, if you get to a record, examine it, and move to the next record, you cannot refer back to a record you left behind If you create a query based on data from one or more tables and allows the users to navigate back and forth with the ability to make changes to records, this type of record set is referred to as Dynamic.

Normally, you firstly create a record set by populating a table with the desired values. Besides this technique, we mentioned in the first lesson that there were various libraries you can use to create your database. Each one of these libraries provides a means of creating a record set. To be an effective database developer, you should know how and when to create a Recordset object.

In the strict sense, you cannot visually create a Recordset in Microsoft Access, that is, from the Database window. Instead you must write code (in the next sections, we will see that a form you create provides you with a Recordset object you can use directly). Based on this, to programmatically create a Recordset object using the built-in library of Microsoft Access, you can start by declaring a variable of type Recordset. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rst As Recordset
End Sub

This is essentially the same technique you use in DAO except that you must declare the variable as being of type DAO.Recordset.

To create a record set using ADO, start by declaring a variable of type ADODB.Recordset. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstPersons As ADODB.Recordset
    
End Sub

Before using the record set, use the New operator to allocate memory for it. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstPersons As ADODB.Recordset
    
    Set rstPersons = New ADODB.Recordset
End Sub

After declaring the variable, you must define the source of its records. Of course, you have various alternatives.

 

The Recordset Object of an Object

In previous lessons, we saw that some controls, such as the combo box or the list box, were meant to hold a list of values. We also know that a form or a report is primarily created to show a record. Such controls hold their own record set. If you create a Recordset object and want to initialize it with the values held in the form where it is called, you can simply assign it Me.Recordset. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rst As Recordset
    
    ' Specify that the record set points to the records of this form
    Set rst = Me.Recordset
End Sub

This means that, when a form is equipped to display the values of a list, that form has a Recordset object that represents its records. Once again, remember that there various other ways you can initialize a Recordset Object. For example, if you are using ADO, to use the records of the form that is calling it, you can assign Me.Recordset to your Recordset object. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstPersons As ADODB.Recordset
    
    Set rstPersons = Me.Recordset
End Sub

We saw that the Windows controls on a form could be used to display data from a table. This is done by specifying a list of values in the RecordSource property of the form. To get the set of records that the RecordSource property of a form holds, you can access its RecordsetClone property.