Microsoft Access VBA Macro Programming

Chapter 29 Monitoring Table Statistics

Chapter 29 Monitoring Table Statistics

I

n large complex Access applications, it is difficult to keep track of what is actually
going on within the database. Tables grow and change in size, particularly if large
amounts of data are being imported from external sources.
By seeing how table sizes are changing according to what is going on, developers and
some users often get a feel for whether the application is running correctly or not. They often
know, for example, how many records they are going to receive from an external data source,
so they will expect a particular table receiving this import to grow in size by exactly this
number.
Many people refer to these as “sanity” or “health” checks on the database. They use this
kind of data for reconciling the tables and checking that no extraneous data is appearing or
that records are disappearing unexpectedly.
You can monitor tables quite easily by way of VBA procedures. In this example, we will
use the Northwind database. You can find this by loading Access and clicking Sample in the
left-hand Navigation pane of the opening window. Double-click the Northwind icon.
You first need to create a table to hold the table statistics. Click Create on the Access
menu and then click the Table Design icon in the Tables group of the ribbon.
Create a table with three fields in it:

 TableName Text (255 characters)

 TableSize Number (Long Integer)

 TimeStamp Date/Time

305