Compacting an Access database is important because it clears out the replaced objects records and organizes the data according to the primary keys in each table. This can only happen when no one is using the database.
The easiest way to do this is with a macro that runs at a certain time of the day. You can do this with VBA but if the user doesn't allow VBA to run for security concerns, this won't happen.
How to setup your shutdown macro
In a form that is always open, be it the main form in your database or a hidden form, you need to add a timer interval of 20,000 milliseconds or more and a Timer Event as shown in Figure 1.
Figure 1 - This is a (hidden) form that has an embedded macro in the Forms Timer Event
Now you need to setup a Macro that looks like Figure 2. In the macro I have added a MessageBox to help you test the Macro. You will need to remove this as the message box will stop the database automatically closing down.
The critical code in the macro to start the shutdown is in the If Event
If Time()>#7:00:00 PM# then
Figure 2 - The macro that closes down the database
and here is the same macro in Access 2007 where VBA is not yet enabled
Figure 3: Here is the same macro in Access 2007 - Also shown is VBA security warning
Firing of the Hidden Form
You need to open the hidden form in your Autoexec macro. Do not open it using VBA as VBA may not be enabled.
Getting Into The Database After The Shutdown Time
Hold the Shift key down as you open the database if you keep getting shut out of the database.
Your Database Is Now Ready For Compacting