What are Recordsets

In this blog post we will be introducing recordsets and providing some examples of their use.

So, what is a recordset? You can think of a recordset as a table or query that we can utilise (read, update, delete, insert) but cannot see. When we open a recordset, the recordset itself is stored in memory and we are able to loop through the records one at a time, manipulating the data as we go.

Recordsets enable us to reference field names, search for records, filter records, count records and much more. With recordsets, we can truly interact with the data stored in our databases. There are two types of recordsets, DAO and ADODB. They both have similar functions and similar operation speeds (DAO is a bit faster) but as a general rule use DAO if you are referencing standard Access tables within your database and use ADODB when you are referencing tables held outside of your Access application (SQL Server for instance).

In order to use recordsets, we need to reference certain libraries. As standard, the DAO library is already referenced in Access whilst the ADO library is not.

Checking DAO is Referenced

Open a new module and enter the following code.  In the immediate window execute the function by entering testDAO and pressing the return key.

Sub testDAO()
    'This sub-procedure loops through all current
    'references and looks for a DAO reference
    Dim A As Variant
    For Each A In Application.References
        If A.Name = "DAO" Then
            MsgBox "DAO Library loaded!"
            Exit Sub
        End If
    Next
    MsgBox "DAO Library NOT loaded"
End Sub

Checking ADO is Referenced

Open a new module and enter the following code.  In the immediate window execute testADO by entering testADO and pressing the return key.

Sub testADO()
    'This sub-procedure loops through all current
    'references and looks for a ADO reference
    Dim A As Variant
    For Each A In Application.References
        If A.Name = "ADODB" Then
            MsgBox "ADO Library loaded!"
            Exit Sub
        End If
    Next
    MsgBox "ADO Library NOT loaded"
End Sub

Adding Missing DAO and ADO References

If either DAO or ADO is missing we need to add them! To do this we need the References dialog box which can be opened by clicking on References in the Tools drop-down menu.

Adding DAO References

To set the DAO reference find the references below (they are dependent on your version of Access).

Microsoft Office 12.0 Access Database Engine Objects Library (Access 2007)

Microsoft Office 14.0 Access Database Engine Objects Library (Access 2010)

Microsoft Office 15.0 Access Database Engine Objects Library (Access 2013)

Adding ADO References

To set the ADO reference find the references below (they are dependent on your version of Access).

Microsoft ActiveX Data Objects 6.0 Library (Access 2007)

Microsoft ActiveX Data Objects 6.1 Library (Access 2010/2013)

Again, Check DAO and ADO References

To check if the above referencing has worked, rerun the two test sub-procedures.

Declaring a Recordset Object

In order to use a recordset, you will need to declare it! Here we provide you with the most standard way to declare a recordset object.

DAO Recordsets

To declare a DAO Recordset object in a module, use the following code:

Sub declareDAORecordset()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblStudents")
    'Opens tblStudents in memory. We can access all the
    'data stored in tblStudents but we cannot see the
    'table itself
    
    With rs
        .MoveLast
        .MoveFirst
        'These two lines of code are necessary to ensure
        'that the recordcount property works correctly
        
        Debug.Print .RecordCount
        'Here we are printing the number of records in
        'tblStudents to the immediate window
    End With

End Sub

ADODB Recordsets

To declare an ADODB Recordset object in a module we can use the following code:

Sub declareADODBRecordset()
    
    Dim rs As New ADODB.Recordset
    rs.Open "tblStudents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    'Opens tblStudents in memory. We can access all the
    'data stored in tblStudents but we cannot see the
    'table itself
    
    With rs
        .MoveLast
        .MoveFirst
        'These two lines of code are necessary to ensure
        'that the recordcount property works correctly
        
        Debug.Print .RecordCount
        'Here we are printing the number of records in
        'tblStudents to the immediate window
    End With

End Sub

DAO vs ADODB

As stated above, use DAO if you are referencing standard Access tables within your database and use ADODB when you are referencing tables held outside of your Access application (SQL Server for instance). You should try to become familiar with both DAO and ADODB objects as this will give you more flexibility going forward.

Typical DAO Recordset Code

Here we provide you with some typical code that you can use to loop through a recordset.

Sub typicalDAORecordset()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblStudents")
    'Opens table students in memory
    
    With rs
        If Not .BOF And Not .EOF Then
            'Ensures the recordset contains some data
            
            .MoveLast
            .MoveFirst
            'Not necessary but good practice
            
            Do While (Not .EOF)
                Debug.Print .Fields("FirstName")
                'Prints the contents of the FirstName field
                'to the immediate window
                
                .MoveNext
                'Very important - otherwise we will be stuck in
                'a loop forever...............................
            Loop
        End If
        .Close
        'close the recordset...
    End With
    
    Set rs = Nothing
    Set db = Nothing
    'and set all objects to nothing!

End Sub

Typical ADODB Recordset Code

Here we provide you with some typical code that you can use to loop through a recordset.

Sub typicalADODBRecordset()

    Dim rs As New ADODB.Recordset
    rs.Open "tblStudents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    'Opens table students in memory
    
    With rs
        If Not .BOF And Not .EOF Then
            'Ensures the recordset contains some data
            
            .MoveLast
            .MoveFirst
            'Not necessary but good practice
            
            Do While (Not .EOF)
                Debug.Print .Fields("FirstName")
                'Prints the contents of the FirstName field
                'to the immediate window
                
                .MoveNext
                'Very important - otherwise we will be stuck in
                'a loop forever...............................
            Loop
        End If
        .Close
        'close the recordset...
    End With
    
    Set rs = Nothing
    'and set all objects to nothing!

End Sub

In this post, we have explained what recordsets are and provided some code to get you started!

Related Posts