Automate and schedule Microsoft Access macros

If you are running various Microsoft Access databases (haven't quite gotten to SQL yet...) which need to be updated, it's usually nice to be able to automate the updates. While there is no built-in scheduler within Access, you can call macros from the command line, which then means you can script macros through the use of a batch file.

1. Create a new text file with an extension of .bat. You can use save as, or unhide extensions to use the .bat extension

2. In that file, put the full path to the Access executable surrounded by quotes, which will be

Office 2007 - C:\Program Files\Microsoft Office\Office12\MSACCESS.exe

Office 2003 - C:\Program Files\Microsoft Office\Office11\MSACCESS.exe

depending on the version of Access you're using.

3. Now put a space, then add the full path to the Access file you want to automate surrounded by quotes, including the file name. For example, "C:\Access Files\MyDatabase.mdb"

4. Now add a space to the end of that, and add the /x switch, followed by a space, then put the macro name (no quotes needed).

5. Save your .bat file, making sure it stays in .bat format.

6. Go to Start->All Programs->Accessories->System Tools->Scheduled Tasks, and create a new task. When asked what you want to run, browse to your .bat file and select that. Choose the rest of the options according to whatever schedule you want to put the macro updater on

In the end you should have a single line in your batch script that looks something like

"C:\Program Files\Microsoft Office\Office11\MSACCESS.exe" "C:\Access Files\MyDatabase.mdb" /x AutoMacroName

That's it. You now have a fully automated macro on a schedule. There used to be a point in time where we had a user who would start a macro when they arrived in the morning, and then sometime in the afternoon they'd be able to use the updated database. That's very inefficient, and can be a huge waste of time. This automation process can be a great time saver and allow you to update information after-hours, rather than making someone do it manually and wait for the process to finish.

The next thing you might want to look into is adding an email notification to your automated process so you know whether or not it finished. To do that, check out my other post about adding email notifications to ntbackup. The process behind both are practically the same. If you're interesting in more scripting, do a quick search and you should find a few more articles on the site too.

For more command-line switches for Microsoft Access, check out KB 209207