Create a Scheduled Job in MSSQL from MS ACCESS and VBA

anthonytr
anthonytr used Ask the Experts™
on
Hi,

I like the ability in MS SQL to create Scheduled Jobs within SQL Server Agent to run stored procedures etc in my SQL Database.  What I am looking at setting up is to allow a user within MS Access to actually create a Schedule Job Specification from scratch without having to log into MS SQL Server studio.

Is this possible?  I have searched on google and haven't come across anything yet.

If this isn't possible, can anyone suggest a workaround within MS Access which would enable a user to create a scheduled job for a given time (say run every Monday or Every 2 weeks on a Wednesday) and then link that with a SP to run at that time and create a specific record in my database when it runs?

Help gratefully appreciated,
Anthony
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you can execute a stored procedure in SQL Server from MS Access db then you can (potentially) write your very own wizard in MS-Access itself.

What a wizard does actually is executes a set of stored procedure with appropriate parameters.

Here is a list of all related stored procedures.
When I say "If you can execute..." all I am doing is politely asking "If you know how to execute...".

Executing stored procedure from MS-Access is routine practice.

Author

Commented:
Hi,

Yes, I know how to execute a SP - thanks.  

I was unaware that it was possible to create scheduled jobs this way, thank you for pointing this out.

If I wanted to create a way of setting a scheduled job or action in MS Access, can you think of other ways of going about it?

As an example, we service and carry out maintenance on security equipment.  Our clients have scheduled dates when these 'jobs' are carried out.  Each job has a work order which needs to be created so actions can be logged and billed for.  What would be a good method or process to carry this out in MS Access, to auto generate the appropriate Job Sheets for each customer on their scheduled dates?

Thanks
If you believe that every step of the above process can be achieved within database, then set of your own stored procedures executed within a scheduled job is best option. I am assuming that the data is in SQL Server. If not then following applies....

When I say "can be achieved within database" it only means, you don't want to do any email sending (which can be done in SQL but not recommended!), copy some files and stuff like that which would go out of db domain.

If answer to above is 'Yes' I would take C# console application (because I am comfortable with it) which would do both db and non db stuff and this can be executed as a schedule task (which would be done by OS!).

Essentially, the nature of such processes is being scheduled for execution, which can be done by some process which always running. I am unaware how this can be done in Access as it runs only when we open it.

Author

Commented:
Thank you !  Very useful indeed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial