Scheduling an Access Task
If you need to print a report, or do some other Access task, on a regular basis, you can do this using a VBScript, a batch file and the Windows Task Scheduler. This article shows how.
The sample database, Scheduling Task (AA 252).mdb, has some tables from the old Northwind sample database, and a simple Orders report. The mcrPrintReport macro prints the report using the OpenReport macro action.
The VBScript is listed below. It was created in Notepad and saved with a .vbs extension. VBScript is a cut-down version of VBA, lacking error trapping and lots of other stuff, but it is useful for simple tasks. Modify the database path as needed for your computer.
Set appAccess = CreateObject("Access.Application")
strDBNameAndPath = "C:\Users\Helen Feddema\Documents\Writing\WAW\Scheduling Task (AA 252).mdb" appAccess.Visible = True appAccess.OpenCurrentDatabase strDBNameAndPathappAccess.DoCmd.RunMacro "mcrPrintOrdersReport" appAccess.CloseCurrentDatabase Set appAccess = Nothing
Years ago, I was able to run VBScripts (.vbs files) directly from the Task Scheduler, but this no longer works. However, there is a simple workaround: make an old-fashioned batch file to call the VBScript, using Notepad, and save it with the .bat extension. Here is the batch file; modify the database path as needed for your computer.
cscript "C:\Users\Helen Feddema\Documents\Writing\WAW\PrintReport.vbs"
Windows Task Scheduler
To set up a scheduled task, open the Windows Task Scheduler (it is in the Windows Administrative Tools group):
Figure A. Opening the Task Scheduler
Select Create Basic Task in the main Task Scheduler window:
Figure B. Creating a basic task
The Create Basic Task Wizard steps you through creating a task – on the first screen, enter the task name and description:
Figure C. Naming the new task
Click Next to get to the next screen, where you create the task trigger:
Figure D. Selecting the task trigger
On the next screen, you can specify the trigger more precisely:
Figure E. Refining the trigger
On the next screen, accept the default setting of Start a program:
Figure F. Accepting the Start a program option
On the next screen, browse for the batch file:
Figure G. Browsing for the batch file
The final screen of the Wizard summarizes the new task:
Figure H. The new task summarized
Click Finish to save the task. The new task now appears in the Task Scheduler Library:
Figure I. The new task in the Task Scheduler Library
Now the task action should fire according to the triggers you set for it.