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.
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.
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.
|Previous||Copyright © 2005-2010 FunctionX, Inc.||Next|