Create a Scheduled Job in MSSQL from MS ACCESS and VBA
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.
ASKER
Thank you ! Very useful indeed.
Executing stored procedure from MS-Access is routine practice.