How to: Count the Number of Records in a DAO Recordset

  • Article
Access Developer Reference

You may want to know the number of records in a Recordset object. For example, you may want to create a form that shows how many records are in each of the tables in a database. Or you may want to change the appearance of a form or report based on the number of records it includes.

The RecordCount property contains the number of records in a table-type Recordset or the total number of records accessed in a dynaset- or snapshot-type Recordset. A Recordset object with no records has a RecordCount property value of 0.

Bb243791.vs_note(en-us,office.12).gif  Note
The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record. If you check the RecordCount property immediately after creating the dynaset or snapshot (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset, then use MoveFirst to return to the first record. This is not done automatically because it may be slow, especially for large result sets.

When you open a table-type Recordset object, you effectively visit all of the records in the underlying table, and the value of the RecordCount property equals the number of records in the table as soon as the Recordset is opened. Canceled transactions may make the value of the RecordCount property out-of-date in some multiuser situations. Compacting the database restores the table's record count to the correct value.

The following example creates a snapshot-type Recordset object, and then determines the number of records in the Recordset:

  Function FindRecordCount(strSQL As String) As Long

Dim dbsNorthwind As DAO.Database
Dim rstRecords As DAO.Recordset


On Error GoTo ErrorHandler


Set dbsNorthwind = CurrentDb


Set rstRecords = dbsNorthwind.OpenRecordset(strSQL)


If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If


rstRecords.Close
dbsNorthwind.Close


Set rstRecords = Nothing
Set dbsNorthwind = Nothing


Exit Function


ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

As your application deletes records in a dynaset-type Recordset, the value of the RecordCount property decreases. However, in a multiuser environment, records deleted by other users are not reflected in the value of the RecordCount property until the current record is positioned on a deleted record. At that time, the setting of the RecordCount property decreases by one. Using the Requery method on a Recordset, followed by the MoveLast method, sets the RecordCount property to the current total number of records in the Recordset.

A snapshot-type Recordset object is static and the value of its RecordCount property does not change when you add or delete records in the snapshot's underlying table.