Running a DTS package or Job from a Stored Procedure

[NewGuy2006]

Good morning All

I am looking for some guidance I am working on a job that requires the end user to have a access front end (.adp)linked to a SQL server database, however the process requires them to run some DTS packages.
what I am looking for is there a simple way to run the DTS package(s) or a job(s) in a stored procedure so I can call this stored procedure from the front of the access .adp

Unless anyone knows a clever way to call the DTS package straight from the access .adp

thanks in advance

 

[RobertT687]

Yes there is an indirect way to initiate a DTS from Access.

First:
Create a Job on your SQL server that has your DTS task as a Job step running an operating system command, such as:

DTSRun /S /E /N "YourDTSPackageName"

The flags are:
/S = run on current server
/E = run as a trusted connection
/N = DTSPackageName

Enable the job, but do not schedule it.

Next:
In Access create a ADODB connection to the 'msdb' database.
Then, assuming your connection is called cnn, the following will start the job:

cnn.Execute ("EXEC sp_start_job @job_name= 'YourJobName'")

Lastly:
Close the connection.