RecordCount Property

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object, TableDef object.

Description

Returns the number of records accessed in a Recordset object, or the total number of records in a table-type Recordset or TableDef object.

Return Values

The return value is a Long data type.

Remarks

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only-type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Note Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.

As your application deletes records in a dynaset-type Recordset object, the value of the RecordCount property decreases. However, records deleted by other users aren't reflected by the RecordCount property until the current record is positioned to a deleted record. If you execute a transaction that affects the RecordCount property setting and you subsequently roll back the transaction, the RecordCount property won't reflect the actual number of remaining records.

The RecordCount property of a snapshot- or forward-only-type Recordset object isn't affected by changes in the underlying tables.

A Recordset or TableDef object with no records has a RecordCount property setting of 0.

When you work with linked TableDef objects, the RecordCount property setting is always – 1.

Using the Requery method on a Recordset object resets the RecordCount property just as if the query were re-executed.

See Also Count function ("SQL Language Reference" in Volume 1), MoveFirst, MoveLast, MoveNext, MovePrevious methods, Requery method.

Example

This example demonstrates the RecordCount property with different types of Recordsets before and after they're populated.

Sub RecordCountX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    With dbsNorthwind
        ' Open table-type Recordset and show RecordCount
        ' property.
        Set rstEmployees = .OpenRecordset("Employees")
        Debug.Print _
            "Table-type recordset from Employees table"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount
        rstEmployees.Close

        ' Open dynaset-type Recordset and show RecordCount
        ' property before populating the Recordset.
        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenDynaset)
        Debug.Print "Dynaset-type recordset " & _
            "from Employees table before MoveLast"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount

        ' Show the RecordCount property after populating the
        ' Recordset.
        rstEmployees.MoveLast
        Debug.Print "Dynaset-type recordset " & _
            "from Employees table after MoveLast"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount
        rstEmployees.Close

        ' Open snapshot-type Recordset and show RecordCount
        ' property before populating the Recordset.
        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenSnapshot)
        Debug.Print "Snapshot-type recordset " & _
            "from Employees table before MoveLast"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount

        ' Show the RecordCount property after populating the
        ' Recordset.
        rstEmployees.MoveLast
        Debug.Print "Snapshot-type recordset " & _
            "from Employees table after MoveLast"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount
        rstEmployees.Close

        ' Open forward-only-type Recordset and show
        ' RecordCount property before populating the
        ' Recordset.
        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenForwardOnly)
        Debug.Print "Forward-only-type recordset " & _
            "from Employees table before MoveLast"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount
        ' Show the RecordCount property after calling the
        ' MoveNext method.
        rstEmployees.MoveNext
        Debug.Print "Forward-only-type recordset " & _
            "from Employees table after MoveNext"
        Debug.Print "    RecordCount = " & _
            rstEmployees.RecordCount
        rstEmployees.Close

        .Close
    End With

End Sub

Example (Microsoft Access)

The following example creates a Recordset object based on the Orders table and then determines the number of records in the Recordset object:

Sub CountRecords()
    Dim dbs As Database, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("Orders")
    Debug.Print rst.RecordCount
    rst.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example displays the number of records in the Customer recordset in the Nwindex.mdb database.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path _
    & "\Nwindex.mdb")
Set rs = db.OpenRecordset("Customer")
On Error GoTo errorHandler
rs.MoveLast
MsgBox "There are " & rs.RecordCount & " records in " _
    & rs.Name
rs.Close
db.Close
    Exit Sub
errorHandler:
    MsgBox "There are no records in " & rs.Name
    rs.Close
    db.Close