Skip to content

Scheduling an Access Task

Introduction

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.

Access Database

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.

VBScript

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 strDBNameAndPath

appAccess.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.

About this author