No primary key is required since this is an internal table for information only. Save the table
with the nametblTableStatistics.
Enter the following code into a module:Sub TableStatistics()
Dim RecSet As Recordset, RecSet 1 As Recordset, TDef As TableDef
CurrentDb.Execute "delete * from tblTableStatistics" 'Optional
Set RecSet = CurrentDb.OpenRecordset("tblTableStatistics")
For Each TDef In CurrentDb.TableDefsSet RecSet 1 = CurrentDb.OpenRecordset(TDef.Name)
RecSet.AddNew
RecSet!TableName = TDef.Name
RecSet!TableSize = RecSet1.RecordCount
RecSet!TimeStamp = Now()
RecSet.UpdateNext TDef
Set RecSet = Nothing
Set RecSet 1 = Nothing
End SubThis code uses theDimstatement to set up objects for two recordsets and a table
definition object. ADeletestatement is used to clear previous data from the table. This
statement is optional in that the table could keep accumulating statistical data on the tables.
Since there is aDate/Timefield, all you would then need to do is query on a specific range
of date/time to find out what the state of the database was at that point.
The process iterates through all theTableDefsin theTableDefscollection of the database.
Using the currentTableDefname, a second recordset is opened in order to get the number of
records for that table definition.
A new record is added to the tblTableStatistics recordset and the TableName, TableSize,
and TimeStamp fields are populated and updated.
If you run this and then open the table tblTableStatistics, you will see details for all tables
within the database at a particular date and time.
Coupled with Chapter 25, this provides a very useful tool to keep track of exactly what is
going on in the application. You could set it on a timer on the opening form so it is fired off
every hour, and also fire it off immediately before and after any data import.
I have found this methodology very useful for convincing the most doubtful manager
about the integrity of the database!306 Microsoft Access 2010 VBA Macro Programming