Scheduling an Access Task
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.