HOW TO: Run Scheduled Tasks with Microsoft Access

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 /cmd flag.

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 VBA.Command function.  

You can use this function at startup to redirect your code to perform whatever the recurring task is.  

Practical Example

1. Create a new database
2. 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

3. Create a new macro
4. Choose "RunCode" from the "Add New Action" dropdown
5. Set "Function Name" to Startup()
6. 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.

Some Notes

• AutoExec is a special macro that runs automatically at startup
• Even though the Startup() routine returns no value, we declare it as a Function because we can't directly call a Sub from a macro in Access
• Under normal usage, the cmd window code gets skipped
• I explicitly call Application.Quit inside my "Nightly" block to avoid Access hanging on code that requires user interaction (such as the MsgBox code in my example)
• I always call Trim() around the VBA.Command function to avoid bugs introduced by leading or trailing whitespace on the command line
• As an alternative to /cmd, you can also use the /x switch 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 Autoexec and Autokeys because 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

Image by chenspec from Pixabay