Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Create a Scheduled Job in MSSQL from MS ACCESS and VBA


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,
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of anthonytr



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?

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.
Thank you !  Very useful indeed.