Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Schedule a task to run few Access Queries

Posted on 2013-10-23
12
Medium Priority
?
1,360 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 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

661 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