• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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.
0
snydero
Asked:
snydero
  • 2
  • 2
1 Solution
 
Victor SpiridonovCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now