Solved

Schedule a task to run few Access Queries

Posted on 2013-10-23
12
1,201 Views
Last Modified: 2013-11-12
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
0
Comment
Question by:at999
  • 6
  • 6
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
...etc

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"
    'etc
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


JeffCoachman
0
 

Author Comment

by:at999
Comment Utility
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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?
...etc.

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.

JeffCoachman
0
 

Author Comment

by:at999
Comment Utility
Jeff,

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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?
0
 

Author Comment

by:at999
Comment Utility
query2 inserts and other ones just updates those inserted rows
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yes but why cant one query do all the updates?
0
 

Author Comment

by:at999
Comment Utility
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..
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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?
0
 

Author Comment

by:at999
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Then you can use the steps I outlined in my first post to set up a scheduled task to do this...
0
 

Author Comment

by:at999
Comment Utility
ok.. thanks a lot
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now