MS Access front end to SQL

[MrMystery]

I have a DTS which is scheduled to run at midnight each night. It is simply a T-SQL query which refreshes the contents of a transaction table with that days new transactions.
However, there are occasions where I would like to run this DTS or the T-SQL query ad-hoc.
I would like to provide this option to a non-technical user without access to Enterprise Manager etc.
Can I create an MS Access front end with a button that runs the query?
Please advise!

 

[JohnDTampaBay]

Seems easy enough. I would move the query from the DTS package into a stored procedure. This encapulates the process of populating the table. Have the DTS package and Access both call the stored procedure.

I am not an Access developer. You may have to post in that forum. I know you can create an Access applet that connects to SQL Server. Shouldn't be hard to bind a stored procedure call to a button. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[RobertT687]

Yes, absolutely.
1) Make note of the precise name you gave the scheduled job.
2) In Access, have the following in the On_Click event of the button to run the job:
Dim SQLCmd as String
SQLCmd = "EXEC sp_start_job @jobname='your exact job name'"
Currentdb.Execcute (SQLCmd)