Avatar of anthonytr
anthonytr
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
SQLVBAMicrosoft Access

Avatar of undefined
Last Comment
anthonytr

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Nitin Sontakke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Nitin Sontakke

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.
anthonytr

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
Nitin Sontakke

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
anthonytr

ASKER
Thank you !  Very useful indeed.