SELECT COUNT (*) FROM table

' Return the number of records in the table.
Private Sub cmdCount_Click()
Dim db As Database
Dim rs As Recordset

    On Error GoTo DataError
    
    ' Open the database.
    Set db = OpenDatabase(txtDatabase.Text)

    ' Open the recordset.
    Set rs = db.openrecordset( _
        "SELECT COUNT (*) FROM " & txtTable.Text)
        
    ' Display the result.
    lblCount.Caption = "Table has " & _
        Format$(rs.fields(0)) & " records."

    rs.Close
    db.Close
    
    Exit Sub

DataError:
    MsgBox "Error " & Err.Number & _
        " counting records." & _
        vbCrLf & vbCrLf & Err.Description
End Sub