Accessing Data with VBA Code - Introducing the DAO Recordset

This post is all about how we can use VBA code to access the data stored in our database.  By access, I mean, being able to add, find, edit or delete records.  In so doing we are effectively automating data access thereby enabling us to create some very powerful database applications.

Creating a Reference

To do this we are going to make use of Data Access Objects (DAO) which is contained in the Microsoft Office 12 Access database engine Object Library.  The very first thing we must do is create a reference to this library if it does not already exist.  The procedure for doing this is really quite simple:

1. Open the VBA Editor.
2. Open the TOOLS menu.
3. Select REFERENCES from the TOOLS menu.
4. When the REFERENCES dialog form opens, check to see whether Microsoft Office 12 Access database engine Object Library has a tick against it.  If it has it should be towards the top of the list (all checked references appear above the others, and those that are not, appear in alphabetical order below).
5. If the library mentioned in stage 4 is not ticked, you will need to scroll down the list to find it, and then tick the check box.
6. Click OK to close the dialog form.

The reference has been created and you are now ready to start using Data Access Objects.

Introducing the DAO Recordset

The DAO Recordset is basically a VBA object which enables us to gain access to information stored in database tables or queries via code.  It is VBA's representation of the table or query, and we make use of the methods and properties of the DAO Recordset to manipulate the data that it contains.

The first coding task we have to perform is to create an instance of the recordset object.  This is then stored as an object variable. We do this with the following line of code:

Dim rstNameList as DAO.Recordset

This creates an object variable called rstNameList.    Our the next task is to set the value of  this object variable. This effectively means that we are setting  rstNameList to represent a particular table or query in our database.  This is done as follows:

Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

This line of code sets the value of object variable rstNameList to a DAO Recordset based on a table called tblNames.  This is done by using the OpenRecordset method of the Recordset Object.  This is applied against an object reference to the current database - hence our use of the CurrentDb method of the Application object preceding OpenRecordset.  Don't worry if this sounds confusing, just remember to enter the name of your table or query as the first parameter of the OpenRecordset method.  The second parameter, dbOpenDynaset simply opens the recordset as a Dynaset type (which is a particularly versatile type).  

We can now apply the methods and properties of the Recordset class against the object variable rstNameList. As we do so, remember that they are acting on a virtual representation of the database table or query being used.  Lets look at how we can add a record to our table via rstNameList:

Adding Records

Imagine our table is comprised of four fields: Id, FirstName, Surname, and DOB (ie date of birth).  This is the data our table currently holds:

Figure 1: Table data from tblNames.
The rstNameList Recordset Object is based on this table.

The following code is designed to add a new record for a person called Graham Bell:

Private Sub cmdAdd_Click()    

    Dim rstNameList As DAO.Recordset

    Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

    

    rstNameList.AddNew

        rstNameList!FirstName = "Graham"

        rstNameList!Surname = "Bell"

        rstNameList!DOB = #7/10/1970#

    rstNameList.Update

    

    If Not rstNameList Is Nothing Then

        rstNameList.Close: Set rstNameList = Nothing

    End If   

End Sub 

Lets go through this:

This code is triggered when a command button called cmdAdd is clicked by the user.  The code then declares an object variable called rstNameList which is subsequently set to a Recordset based on tblNames.  

The central block of code calls the AddNew method of the Recordset object.  This basically signals that a new record is going to be added to the recordset.  The next three lines reference the FirstName, Surname and DOB fields, setting their values respectively.  As such the FirstName field value is set to "Graham", Surname to "Bell", and DOB to 7/10/1970.  Note the FirstName and Surname fields are of the STRING data type, so are consequently enclosed within quotation marks ie ""; whereas the DOB field is of the DATE data type so is enclosed between hash marks using the American Date Format ie #mm/dd/yyyy#. (Had we used the number data type this would not have needed to be enclosed by anything). Finally the central block of code saves the new record by calling the Update method of the Recordset object.

The final block of code simply closes the recordset object and destroys the object variable reference so as not to use unnecessary memory.

You can now go back to the original table tblNames, and click Refresh to see the new record:

Figure 2: The tblNames table after new record is added via VBA Code.

As you can see, the new record has been added at the bottom of the table.  Note that we did not need to set the value of the ID field as this is of the AUTONUMBER data type and is added by Access automatically.

Editing Records

The code for updating a particular record is very similar to the central block of code in the in the previous example.  The main difference is, however, that we must find the record prior to making any changes.  To do this we are going to use the FindFirst method of the Recordset object.  The syntax for this is as follows:

 rstNameList.FindFirst "Id = 5"

Notice that this method passes the parameter "Id = 5".  This is basically an SQL WHERE clause without the WHERE keyword.  It tells access to find the first record in the recordset where the value of the ID field is 5.  There is more information on SQL Where in my Gentle Introduction to Access SQL. Just remember that the parameter must be enclosed within quotation marks, so if the value searched for is itself a string, you must not only enclose the parameter within quotation marks ie "", but you must also enclose the actual value to be found within single quotation marks ie ' '. For example if you wanted to search for the surname Lang, the syntax would be:

rstNameList.FindFirst "Surname = 'Lang' " 

It is also possible to test whether a record has actually been found using the NoMatch property of the Recordset object as follows:

If rstNameList.NoMatch = false Then

    .... code to run if record is found. 

Else

    ....code to run if no record is found.  

End if

Once we have found the record, we can then go on to edit any, or all, of its fields.  This is done with the following code:

rstNameList.Edit

        rstNameList!DOB = #8/10/1970#

rstNameList.Update

Notice how we have used the Edit method of the Recordset object instead of AddNew.  The rule is use Edit if you want to edit an existing record, and use AddNew if you want to create a new record.  Once the record has been edited it needs to be saved using the Update method as before.  So lets look at the whole code snippet:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)
    
    rstNameList.FindFirst "Id=5"
    If rstNameList.NoMatch = False Then
        rstNameList.Edit
            rstNameList!dob = #8/10/1970#
        rstNameList.Update
    End If
    
If Not rstNameList Is Nothing Then
    rstNameList.Close: Set rstNameList = Nothing
End If

If we refresh the data in tblNames, you can see that our code has put a DOB value in Sally Lang's record:

Figure 3: The record of Sally Lang (ID 5) now has a DOB value
after her record was edited with our code.

Deleting Records

When it comes to deleting a record, we start by finding the particular record that we want to delete.  Again this is achieved using the FindFirst method of the Recordset Object.  So suppose we wanted to delete Graham Bell's record, we could do a FindFirst search on his record ID which is 8.

rstNameList.FindFirst "Id = 8" 

All we do then is apply the Delete method of the Recordset Object as follows:

rstNameList.Delete 

Again, it is a good idea to nest the above line in an If ...Then... Else statement to test that a matching record has been found.  As such the full code to delete record 8 is as follows:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)
    
rstNameList.FindFirst "Id=8"
If rstNameList.NoMatch = False Then
    rstNameList.Delete
Else
    MsgBox "Record Not Found"
End If
    
If Not rstNameList Is Nothing Then
    rstNameList.Close: Set rstNameList = Nothing
End If


The record has now been removed from the table:

Figure 4: Our code has deleted the intended record.

These have all been basic examples to demonstrate how we can access and manipulate data using VBA code.  How we use the Recordset object in a real database application depends entirely on what needs doing. I have found the possible uses of the DAO Recordset to be virtually endless. It is an extremely   powerful tool to draw upon when needed.  As such, I hope to show some applied examples of its possible uses in a future post.