Access 101: How Do I Run A Process Automatically Once A Day? Part I

There are two ways to do this: 1) from within Access, and 2) externally with Windows Scheduler. I talk about method 2 here: How Do I Run A Process Automatically Once A Day? Part 2 .

Suppose you have a macro (mcrImportFiles) that imports data and you want to schedule it for the middle of the night each night -- say 1 am. How would you do it?

First of all, to do this, you need to leave your Access database open all the time (or at least start it every night) because it must be open to work.

Next, you need to create a form that will stay open all the time. Have this form open automatically at Start Up (see How Do I Configure My Database Start Up? ). It can be hidden if you want. (see How Do I Hide a Form But Leave It Running? ).

Every form has at Timer Property and an OnTimer event. Set the Timer property to 60000 (60 seconds). This will cue the form to run the OnTimer event once each minute.

Now, add some code to the OnTimer event. Something like this:

Private Sub Form_Timer()
If Time() >= #1:00:00 AM# And Time() < #1:01:00 AM# Then DoCmd.RunMacro mcrImportFiles End If End Sub This will run the macro each day at 1 am. To make this process even more robust, convert the macro to VBA code (see How Do I Convert A Macro to VBA Code? ) and insert it directly into the OnTimer event. This way, you can trap for any errors and handle them gracefully instead of simply letting your application hang as it would with a macro.