Have you ever wanted to run some Access code on an automatic schedule?
I'll show you how to use an existing Access application to run and exit gracefully when launched via Task Scheduler while still presenting a friendly user interface when launched by an end user.
The key to making it work is the
Passing Command-Line Args to Access
To pass a command-line argument to access, you use the
/cmd command line argument when launching your application:
Specifies that what follows on the command line is the value that will be returned by the Command function. This option must be the last switch on the command line. You can use a semicolon (;) as an alternative to /cmd.
Use this switch to specify a command-line argument that can be used in Visual Basic for Applications (VBA) code
To retrieve the text you pass on the command line following the
/cmd argument, you use the
You can use this function at startup to redirect your code to perform whatever the recurring task is.
- Create a new database
- Add the following code to a new standard module:
Public Function Startup() If Trim(VBA.Command) = "Nightly" Then Shell "winver", vbNormalFocus Application.Quit End If MsgBox "Start up" End Function
- Create a new macro
- Choose "RunCode" from the "Add New Action" dropdown
- Set "Function Name" to
- Close and save the macro as "AutoExec"
Run As a Normal User
To test the application as a normal user, simply compact and repair the database.
You will see a message box that says, "Start up."
Run As a Scheduled Task
To mimic running as a scheduled task, close your database and launch it with the following command:
"C:\Path\To\msaccess.exe" "C:\Path\To\MyDb.accdb" /cmd Nightly
Access will flash briefly on screen, then you will see the "About Windows" dialog box, and Access will close itself.
AutoExecis a special macro that runs automatically at startup
- Even though the
Startup()routine returns no value, we declare it as a
Functionbecause we can't directly call a
Subfrom a macro in Access
- Under normal usage, the cmd window code gets skipped
- I explicitly call
Application.Quitinside my "Nightly" block to avoid Access hanging on code that requires user interaction (such as the MsgBox code in my example)
- I always call
VBA.Commandfunction to avoid bugs introduced by leading or trailing whitespace on the command line
- As an alternative to
/cmd, you can also use the
/xswitch and pass it the name of a custom macro (I don't do that because I hate macros with the passion of a thousand white hot suns; the only two macros I ever use are
Autokeysbecause they provide special functionality)
A Final Note About the Task Scheduler and Automating Other Office Applications
If your task does not seem to be working when you set it to run overnight, try switching the scheduled task option to "Run only when user is logged on."
Office applications are not intended to run in what Windows calls "non-interactive" mode. If you set a scheduled task to "Run whether user is logged on or not", that task will run in non-interactive mode. This can cause a variety of problems, especially if you are automating Excel as part of the recurring process.
The simplest solution is to lock the workstation instead of logging off and leave the option set to "Run only when user is logged on."
That may not be practical in your situation, but it's an important consideration to be aware of. Consider yourself warned:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component