Counting records in a Recordset object

Counting records in a Recordset object

If you are using a Recordset object, and wish to get a count of the number of records, you must ensure that you force Access to count all of the records, as by default, Access will not load them all to start with, and the RecordCount returned will be the number of records accessed. You can do this by using the .MoveLast method, but be aware that this may take some time. However, as this then makes the last record the current record, you will usually need to follow this with a .MoveFirst to ensure that you are again at the start of the records:

Function fRecordCount() As Long
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Categories")
    rs.MoveLast
    fRecordCount = rs.RecordCount
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Note that although the Help for Access 97 states:
When you work with linked TableDef objects, the RecordCount property setting is always -1.
this is not true for linked Access tables, and the RecordCount property for these tables is the same as for a native Access table.

Often, you will not necessarily want to know the number of records in a Recordset, but the fact that there are records. In cases such as these, you can just test the RecordCount property to see if it is non zero:

Function fCheckRecords() As Boolean
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Suppliers")
    fCheckRecords = rs.RecordCount
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Alternatively, you can check if both the BOF and EOF properties are True for the Recordset to see if records are returned. BOF returns True if the current position is before the first record, and EOF is True if the position is after the last record. Obviously, the only way that both of these can be true is if no records are returned:

Function fCheckRecords2() As Boolean
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Categories")
    fCheckRecords2 = Not (rs.BOF And rs.EOF)
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

However, although you might be working with a Recordset object, most of the time you do not need to know the number of records returned. For example if you wish to perform an action with all records, you can simply use a Do...Loop Until:

Sub sLoopRecords()
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Categories")
    Do
        Debug.Print rs!CategoryName
        rs.MoveNext
    Loop Until rs.EOF
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Top

Last modified at 06/06/2006 14:55:45