Data Management optimize

Get IT Done: Using DTS and SQL Server Agent to schedule Access tasks

Schedule tasks in Microsoft Access with SQL Server Agent and DTS

A large part of administering any database system is running regularly scheduled tasks, and there are usually a lot of them. In fact, a portion of every workday may be spent running reports and backing up files.

If you're running SQL Server, you can lighten your task load considerably by delegating routine tasks—such as running reports and backing up files—to Data Transformation Services (DTS) and SQL Server Agent. In addition to relieving yourself of a little work, you can rest assured that SQL Server won't forget the print job or be occupied with other important tasks when the boss' assistant stops by to pick up those reports you promised. By following the steps below, you can create a simple ActiveX script that DTS can run to complete a task in Access.

DTS and SQL Server Agent
DTS is a SQL Server utility that organizes connections, tasks, and workflow constraints. The DTS object model contains a number of objects, but we'll use just two of them: the package and task objects. A package is made up of the steps to be performed. A task or step defines the actual commands to be carried out. A package can contain more than one task.

DTS is extremely flexible, and it offers a number of ways to complete specific tasks. In this example, you'll create a new DTS package. Within that package, you'll add an ActiveX task using VBScript that establishes a connection to an Access database and then prints a specific report on a recurring basis.

By default, you can use both VBScript and JScript to create a DTS ActiveX script. In fact, you can use almost any other scripting language once it's installed. Use scripts to:
  • Format and transform the data during a copy task.
  • Use conditional logic to manage workflow.
  • Create, use, and modify values stored in DTS global variables.
  • Manipulate COM objects.
  • Access and manipulate data.

The example script I’ll present later is fairly simple. The one catch is that you must create an instance of an Access application. Once that is created, you can use any command available to the Access application in your script.

Once you've created a DTS package that prints the appropriate Access report, use SQL Server Agent to define the recurring schedule so that the agent can execute the recurring print task as required. SQL Server agent is a utility that helps you schedule tasks to run at a specified time.

Printing the Access report
The first step is to create a new DTS package. You'll need SQL Server's Enterprise Manager for that task. Launch SQL Server's Enterprise Manager and then expand the appropriate server group (these examples are executed on a local server). To create the new package:
  1. Right-click the Data Transformation Services node and choose New Package from the resulting submenu.
  2. Choose ActiveX Script Task from the Task menu.
  3. Enter the simple script shown in Listing A into the DTS New Package window. Don't use the VBA continuation character or force a break in any of the statements.
  4. Enter the name of the function, PrintReport, in the Entry Function control, as shown in Figure A. You'll find it to the left of the script window, near the bottom.
  5. Click Save and name the package PrintReport.bas.

Listing A

Function PrintReport()
 Dim objDB
 Set objDB = CreateObject("Access.Application")
 objDB.OpenCurrentDatabase("C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb")
 objDB.DoCmd.OpenReport "Products by Category", 0
 objDB.CloseCurrentDatabase
 objDB.Quit
 PrintReport = DTSTaskExecResult_Success
End Function


Figure A
Create the ActiveX task.


There are a number of ways to execute a package, but for now, return to the DTS New Package window and right-click the new ActiveX Script Task icon that represents the new package. Select Execute Step from the resulting menu. DTS will open the Northwind database and print the Products By Category report. When the task is complete, DTS will display the message shown in Figure B.

Figure B
DTS lets you know when the job is finished.


Scheduling the print task
Now, you’ll use SQL Server Agent to schedule the print task. In the Local Packages window, right-click the PrintReport task (in the right pane) and choose Schedule Package from the resulting submenu. The Edit Recurring Job Schedule dialog box allows you to time a task, whether it's a one-time event or a recurring task. The settings are self-explanatory. Suppose you want to print the report every Monday morning at 9:00 A.M. In this case, you'd use the settings shown in Figure C.

Figure C
Print the Access report every Monday morning.


More than likely, you'd probably like to see the task printed by SQL Server Agent right now. If that's the case, simply set the date and time for five minutes in the future (from the time you're working through this example). Then, click OK and go get a cup of coffee. When you come back, the Access report should be waiting in your printer tray.

After scheduling the task, you can view and modify it by expanding the SQL Server Agent node (under Management) and then double-clicking the Jobs icon in the right pane. Double-click the PrintReport job to display the PrintReport Properties dialog box shown in Figure D. Use these settings to further administer the print task. Click the Schedules tab to view and modify the current settings.

Figure D
Display the PrintReport Properties dialog box.


One warning
You might have noticed that I used the integer value 0 instead of the acViewNormal constant in the OpenReport method. Remember that the code is actually VBScript—not VBA. SQL Server can't identify the acViewNormal VBA constant. Use integer values instead of VBA constants. You can use the Access Object Browser to learn a constant’s corresponding integer value.

Occasionally, you may run into timing conflicts between Access and the task being executed. If the processor needs more time to complete a task, add a DoEvents() function in the appropriate place to slow things down just a bit.

Delegate, delegate, delegate
Administering a long list of database tasks can be a huge responsibility. Delegate as many tasks as you can using DTS and SQL Server Agent. Simply create a DTS package,  then remove the item from your to-do list and add it to SQL Server Agent's.

Doris Manning also contributed to this article.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments