Schedule a task to run few Access Queries

I have an MS Access 2010 database

it has few Queries which updates the tables in that access database...

I want to schedule a task in "Task Scheduler" to run those Queries on a weekly basis...

how to do that

pl help

thanks a lot
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
That approach would be even more precarious because then you would have have two applications to deal with (and the associated connections...)

However you do this, the issue of; what to do if something goes wrong will still exist.
What if the .net app crashes?
What if the computer is powered down?
What if Access crashes
What if the connection fails
What if a query changes and fails to run?
what if the task does not run and a week is missed?
What if the data is corrupted when the queries run? (power loss)
If these queries depend on each other, what if the first one fails, but the others still run?
What if one query performs an operation (Add, Edit, Delete),  but fails halfway through, ..Do you have a plan to "rollback" these operations?

There are just too many things that can go wrong, ...and even if something does go wrong, how and when would you know, what would the consequences be, ...and what would you do about it, how would you fix it?

Now, ...I am not trying to sound like an alarmist, ...these may all be simple queries and this may always run fine without issue.
But the situation will always arise as to what if something does go wrong, ..Then what?

So perhaps you need to tell us about these queries (what they do) and state why they have to be run weekly.
For example: if you need weekly totals, you can run a query on all the data and select a certain week to be processed.

Jeffrey CoachmanMIS LiasonCommented:
This is always difficult to control...
Many things can happen and prevent the scheduled task from running or completing
Then you have to consider what to do in these cases.
You also need to consider if for any reason the queries fail to run.
There may also be security/permissions issues.

So you need to be sure that you have Iron-clad error handling for the queries and the code (and possibly the macro)

But basically you will create a Public function in a module  to run these queries:

Public Function RunWeeklyQueries()
    Docmd.OpenQuery "Query1"
    Docmd.OpenQuery "Query2"
End Function

Then create a macro to run this function:
Macro Name: mcrRunWeeklyQueries
Action: RunCode
Argument: RunWeeklyQueries()

Then create something like this as the scheduled task:
C:\Program Files\YourOfficeFolder\MSAccess.exe c:\YourFolder\YourDatabase.mdb /x mcrRunWeeklyQueries

Then set this to run Weekly

at999Author Commented:
so, is it easier to write a .net application(which will be scheduled to run on a weekly basis)  using c# which runs those SQLs by connecting to that MSAccess database using ODBC or ADODB ?

pl guide
thanks a lot
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

at999Author Commented:

I went through your ideas..

Basically i have to do this -
I  have a main table - "MainTable" in that access database which needs to be refreshed every week...
I have four Linked tables in My Main Access database -

1. first one is a linked table which links to an Oracle View - mylinktable1
2. Second linked table(mylinktable2) comes from another MDB/access database  - mydb2.mdb
3. Third linked table (mylinktable3) comes from another MDB/access database -mydb3.mdb
4. Fourth linked table (mylinktable4) comes from another MDB/access database -mymdb4.mdb

I have to schedule these queries to be executed one after other -
1. first run myQuery1 which deletes all rows in "MainTable"
2. Then run myQuery2 which inserts all rows in "MainTable" from mylinktable1(Oracle)
3. then run myQuery3 which updates rows in "MainTable" from mylinktable2(mydb2.mdb )
4. then run myQuery4 which updates rows in "MainTable" from mylinktable3(mydb3.mdb )
5. then run myQuery5 which updates rows in "MainTable" from mylinktable4(mydb4.mdb )

how to automate it....

pl help

thanks a lot
Jeffrey CoachmanMIS LiasonCommented:
If all the tables are linked then I don't even see the need for the Main table.

As far as I can tell, all you need to do is create a query that displays the "current" (weeks) data.

Not sure why you need three tables to update the main table, so I will presume that query1 inserts the records and the other queries just update different fields.
Is this correct?
at999Author Commented:
query2 inserts and other ones just updates those inserted rows
Jeffrey CoachmanMIS LiasonCommented:
Yes but why cant one query do all the updates?
at999Author Commented:
OK I can write two queries - one for insert and other for updates...

But how to automate it - to run those two queries as a scheduled task..
Jeffrey CoachmanMIS LiasonCommented:
First I will say that you can very well use the "Scheduled task" approach I outlined in my first post, and perhaps you will never have a problem.

But I have to also ask what were you using the table for?
Just to "view" the data,...or were you using the table as the source for something else?
at999Author Commented:
the users will import the data into another application made using java or .net i dont know... but the users want a final msaccess table...

thanks a lot
Jeffrey CoachmanMIS LiasonCommented:
Then you can use the steps I outlined in my first post to set up a scheduled task to do this...
at999Author Commented:
ok.. thanks a lot
All Courses

From novice to tech pro — start learning today.