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
LVL 1
anthonytrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
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.
0
anthonytrAuthor 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
0
Nitin SontakkeDeveloperCommented:
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.
0
anthonytrAuthor Commented:
Thank you !  Very useful indeed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.