RecordCount Property

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object, TableDef Object.

Description

Returns the number of records accessed in a Dynaset-type or Snapshot-type Recordset object, or the total number of records in a Table-type Recordset or TableDef object. This property setting is read-only.

Return Values

The return value is a Long Integer.

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- or snapshot-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.

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 subsequently rolls back the transaction, the RecordCount property won't reflect the actual number of remaining records.

The RecordCount property of a snapshot-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 working with attached 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

MoveFirst, MoveLast, MoveNext, MovePrevious Methods; Requery Method.

Example

This example creates a Recordset object based on the Employees table and then determines the number of records in the Recordset.

Dim dbsNorthwind As Database, rstEmployees As Recordset
Dim lngTotal As Long
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")

Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
rstEmployees.MoveLast
lngTotal = rstEmployees.RecordCount
If lngTotal = -1 Then
    Debug.Print "Count not available."
End If

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, rstOrders As Recordset
    Dim lngTotal As Long

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders")
    ' Fully populate Recordset object.
    rstOrders.MoveLast
    Debug.Print rstOrders.RecordCount
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.

Sub CountRecords()
    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
End Sub