Create MS Access Macro to 'Kick-off' SQL Sched. Task

I need to 'kick-off' a scheduled task (or ISQL script) in MS SQL Server from users' desktops that have Access 97.  Can a macro in Access do this?
Please give example.  SQL Server is 6.5.
LVL 1
snyderoAsked:
Who is Participating?
 
Victor SpiridonovConnect With a Mentor Commented:
It depends on the type of the sheduled task. If you can put all , what your task does into a stored procedure, you can create pass-through query in Access, that will execute this stored procedure  and then use macro to run this pass-through query. If you need help on creating pass-through query, add a comment and I 'll explain further.
0
 
snyderoAuthor Commented:
Yes I can put it all into a stored procedure.  How do I create a pass-through query?
0
 
Victor SpiridonovCommented:
Create new query in Access, when prompted don't select any tables. Go to Query Menu, choose SQL-specific-Pass-through. You will be taken to SQL code window. Type the following code:
"EXECUTE your_procedure_name"
Go to query properties , ODBC Connect str and click on Build button , you will be prompted with standard odbc datasource dialog. Select datasource you wish to connect to,enter user name and password. Close and save the query. Now  you can run the query from database window or macro and it will execute your stored procedure.
0
 
snyderoAuthor Commented:
Thanks.  Can the output (log) of a SQL script be routed to a log file on the server, or back to the user that initiated the stored procedure?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.