ASCII table

The real power of a database is derived from the way you can organize, display and summarize information. You have learned how to put data into databases using VBA and forms. Now it is time to get some meaningful information out.

The process of normalization eliminates redundant information and inconsistences within our database, but it also splits data up into separate tables that we might want to see as a whole. If we do want to view information as a whole, we need to be able to manipulate both records and sets of records with VBA, in the same way that we=ve learned to manipulate forms.

What is a Recordset?

Even if you do not know what a Recordset is, you have already used one. Put simply, a Recordset is just what it says - a set of records. When you open a table in Datasheet view, you are looking at a set of records. When you open a form, it will normally have a set of records behind it which supply the data for the form. Databases are sets of records, and you will find that you make extensive use of Recordset objects throughout your VBA code.

Example:

1. Open up Access. Choose open an existing Database.

2. In the file name text box type s:\dps4a0-01 and press enter.

3. Right click on the whiskey.mdb and choose Copy or press Ctrl+C.

4. In the file name text box type g:\ and get into your user directory. Create a new folder called Recordsets, open this folder. Right click and choose Paste or press Ctrl+V.

5. Open the database called whiskey.mdb.

6. Click on the Modules tab. And click on New to create a new Module.

7. You will create a subprocedure called OpeningARecordset by clicking the Insert menu and selecting Procedure. Fill out the dialog box as shown below

8. Add the following code to the subprocedure:

Private Sub OpeningARecordset()
Dim db As Database
Dim rec As RecordSet
Dim intRecords As Integer

Set db = CurrentDB()
Set rec = db.OpenRecordset(“Whisky”)

IntRecords = rec.RecordCount
MsgBox “There are “ & intRecords & “ records in the Whisky table”
Rec.Close

End Sub

Run the procedure by pressing <F5> or clicking the Run button. You should get the following message box appear.

10. You can check this by clicking on the Table tab and opening the “Whisky” table, you will see that it does in fact have 49 records.
11. Save the module with the name Recordset1

Explaining the Code

1.      The dim statements include new types. Database and Recordset types are the new types, both of these are objects and therefore we can determine which object to use.

2.      The line Set db = CurrentDb() puts the name of the open database into the variable db.

3.      The line Set rec = db.OpenRecordset(“Whisky”) places the contents of the Whisky table into the recordset object variable rec by using the OpenRecordset method.

4.      The line intRecords = rec.RecordCount uses the RecordCount method to count the number of records in the recordset defined by rec.

5.      The message box line you should understand.

6.      The line rec.Close uses the Close method to remove the recordset rec from memory.