Public Sub CountRecords()
' © Arvin Meyer 12/19/2009
' Build a table (tblRecordCounts) with 2 fields (Tablename and RecCount)
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim rstCount As DAO.Recordset
Dim i As Integer
Dim strSQL As String

Set db = CurrentDb

' Clear tblRecordCounts of all records
strSQL = "DELETE * FROM tblRecordCounts;"
db.Execute strSQL

Set rstCount = db.OpenRecordset("tblRecordCounts", dbOpenDynaset)

    For i = 0 To db.TableDefs.Count - 1
    Set tdf = db.TableDefs(i)
        If Mid(tdf.name, 2, 3) <> "sys" Then
            If tdf.name <> "tblRecordCounts" Then
    
                Set rst = db.OpenRecordset(tdf.name, dbOpenSnapshot)
                rst.MoveLast
                    ' Add the table name and record count to tblRecordCounts
                    With rstCount
                        .AddNew
                        !TableName = tdf.name
                        !RecCount = rst.RecordCount
                        .Update
                    End With
                        
                rst.Close
                Set rst = Nothing
            End If
        End If
    Next i

Exit_Here:
    On Error Resume Next
    Set tdf = Nothing
    Set db = Nothing
    rstCount.Close
    Set rstCount = Nothing
    MsgBox "Done!"
Error_Handler:
    Resume Exit_Here
End Sub