RE: Automate Query Runnung

[Tom Wickerath]

Hi Ananth,

It's not clear to me whether or not your queries are all considered action
queries, but here is a procedure that should get you started for the most
common query types in Access. First, set a reference to the DAO Object
Library if it is not already set. Use version 3.6 for Access 2000/2002/2003,
or version 3.51 for Access 97. To check whether or not you already have this
reference set, open a new standard module. Then click on Tools > References.
If you find it checked, then just click on OK to dismiss this dialog. If not,
scroll down the list until you find it, and place a check mark in it to
select it. Then dismiss the references dialog box.

Copy the following code and paste it into your new module.
Notes:
1.) I removed the indentation from the SELECT Case, to help prevent word
wrap in a newsgroup message from splitting a line of code into two lines.

2.) The name of the table referenced is: tblQueries
The names of the queries are in a field named: QueryName

3.) I included a numeric field, indexed unique (no duplicates), which allows
one to specify a sort order. This field is named: RunOrder
This gives you the ability to easily change the order that queries are run,
without having to rename them to match a -number naming convention.

After pasting the code, click on Debug > Compile ProjectName, where
ProjectName is the name of your VBA project (likely the same name as your
database). Fix any compile errors before trying to do anything else.

To run the code, have your blinking mouse cursor anywhere within the
procedure. Then press the F5 button.

Option Compare Database
Option Explicit

Sub RunSavedQueries()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strQueryName As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT QueryName " _
& "FROM tblQueries ORDER BY RunOrder")

With rs
Do Until (.BOF Or .EOF) = True
strQueryName = rs("QueryName")
Set qdf = db.QueryDefs(strQueryName)

Debug.Print strQueryName, qdf.Type

Select Case qdf.Type
Case 0, 16, 128 'Select queries: 0=Select, 16=Crosstab, 128=Union
DoCmd.OpenQuery strQueryName
Case 32, 48, 80 'Action queries: 32=Delete, 48=Update/Append, 80=Make Table
db.Execute strQueryName, dbFailOnError
Case Else
'Do nothing for the present time.
End Select

rs.MoveNext
Loop
End With

ExitProc:
'Cleanup
On Error Resume Next
Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure RunSavedQueries..."
Resume ExitProc
End Sub

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Ananth" wrote:

> I have a Access Table that has 20 queries.
>
> Query-1
> Quert-2 etc upto Query-20
>
> Can somebody help me with a small VB routine or Macro that would run all the
> 20 Queries at a strike of a key (just like we have DO command in Foxpro)

[Ananth]

Many Thanks for your elaborate reply. It is too techincal for me.

I am a novice in VB and in Access Macro.

I have a Access DB called Spend Data 2006 ,that has 5 Tables
Using Access Design Feature, I had created 20 Queries, which are titled
Query 1, Query 2, Query 3 etc…

The DB has a Master Table on which these 20 Queries work. Most of these
queries are update queries, that has run sequentially (starting from query1
and end by executing query 20, which is cross tab query that produces the
results)

What I meant was,I want a solution that will run the 20 queries
automatically, conclude with displaying the cross tab results (20th Query)

[Armen Stein]

I rarely recommend Access Macros for anything, but simply running a
series of queries is one thing they do well. Create a new Macro, and
add these lines:

SetWarnings No
OpenQuery Query1
OpenQuery Query2
etc...
SetWarning Yes

Note that the Yes, No, Query1, etc. are specified in the detail sheet
below, not on the statement itself.

The SetWarnings No statement turns off all the confirmation warnings
for your action queries. If you want to see those, delete that line
from your macro.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Tom Wickerath]

Hi Ananth,

> What I meant was,I want a solution that will run the 20 queries
> automatically, conclude with displaying the cross tab results (20th Query)

The solution I proposed will do exactly that. I even set up a test with each
type of query, and tested it first.

Can you tell me which part(s) of the answer I provided that you do not
understand? I don't necessarily expect you to understand the VBA code, but
you should be able to select the code, copy it (Ctrl C) and paste it into a
new module (Ctrl V).

[Tom Wickerath]

The reason I did not suggest a solution like this is that using Set Warnings
in a macro can be rather dangerous. If any one of the macros fails for any
reason, the macro action at the end to restore warnings will never be run.
The new Access 2007 will provide for the ability to trap for errors in
macros, and respond accordingly, but in Access 2003 and all previous
versions, this is simply not possible. Consider the following quote:

From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151)

"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."

"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[aaron...@gmail.com]

if you were using Access Data Projects; then you could simply automate
the execution of a query or 10-- using SQL agent.

Access MDB isn't an enterprise level reporting platform.. it isn't
stable; it isn't reliable.

anyone that uses MDB anywhere-- for anything-- should be fired and then
SPIT UPON.

-Aaron

> > >Query 1, Query 2, Query 3 etc...