Closing a database at a certain time

<< Click to Display Table of Contents >>

Navigation:  The Toolbox >

Closing a database at a certain time

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


Use my new 123 smart Office shortcuts program to compact your database after everyone is logged out. It only takes one click to set up the compacting as I reported in my blog