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

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.

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.