Solved

Schedule a task to run few Access Queries

Posted on 2013-10-23
12
1,239 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when attempting to use Replace function in Access 2010 sql query 4 18
Combobox row source 2 20
Access Open Report with SQL Parameter 11 29
Filter a form 8 12
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

785 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