count rows in all tables

[oegstgeest]

I like to define a VB script which counts for all existing tables in the DB the number of records and results the output in a format like:

Table1;34 rows
Table2;3456 rows

This enables me to easily see which tables grow fast. Of course you can write a number of queries which totalizes but it is time consuming and as soon as you add or delete tables you need to add or dekete a query.

Thanks,

Mark

 

[dcx693]

Try this code:

Sub TotalRecords()
Dim tdf As DAO.TableDef
Dim lngCount As Long

 For Each tdf In CurrentDb.TableDefs
  If Left(tdf.Name, 4) <> "MSys" Then
   Debug.Print tdf.Name & " ; " & tdf.RecordCount & " rows"
  End If
 Next tdf

End Sub

This outputs the list of tables and their recordcounts to the Debug window.

PS: Is "totalizes" a real word?