Solved

Schedule a task to run few Access Queries

Posted on 2013-10-23
12
1,287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39594434
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
ID: 39594501
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
ID: 39594675
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:at999
ID: 39595069
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
ID: 39595578
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
ID: 39595629
query2 inserts and other ones just updates those inserted rows
0
 
LVL 74

Expert Comment

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

Author Comment

by:at999
ID: 39597319
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
ID: 39597664
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
ID: 39598090
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
ID: 39598536
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
ID: 39598619
ok.. thanks a lot
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

710 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