Can you run multiple queries back to back?

[bbbuffalo]

I have a database with about 200 queries.
I would like to have a way to run all 200 queries and produce some sort of report showing the name of each query and the results, and I would like to do this in 1 step.

Is there a way to tell access to run qry1, then run qry2, qry3, qry4 etc?
Its ok if each query produces its own report, I just don't want to have to manually run all 200.

Thanks in advance, and if you have an answer that involves VB, please explain slowly.

 

[Andrew Fergus]

Hi

This could definitely be done using VBA. Does the sequence in which the queries run matter? Also, why are there 200 queries? Are they 200 different completely queries or is it the same query with different criteria?

Andrew

 

[bbbuffalo]

There are 200 completely different queries

 

[bbbuffalo]

The reason there are so many queries is because the purpose is to test data conversion.

There are dozens of tables and dozens of fields in each table.

I have about 200 queries that check various things
For instance:

Qry1. Dataset1, Field 1 >0
Qry2. Dataset1, Field 2 not null
Qry3. Dataset1, Field 19 = Dataset3 Field 7

and so on.

What I would like is a way to run all queries one after the other automatically.

Is there a way to do that?

 

[Andrew Fergus]

Apologies for not getting back to you - this one slipped under the radar.....

There are a couple of ways of doing this. If you run the queries in alphabetical order, use the following VBA code:

Public Sub RunMyQueries()
'Enable a reference to 'Microsoft DAO' under VBA menu option Tools > References

Dim dbs As Database
Dim qry As QueryDef

Set dbs = CurrentDb()
For Each qry In dbs.QueryDefs
    If Left(qry.Name, 1) <> "~" Then
        DoCmd.OpenQuery (qry.Name)
        'If it is not an action query then you may want to close the query?
    End If
Next

Set qry = Nothing
Set dbs = Nothing

End Sub

If alphabetical order is not an option, then use this code instead (but you will need to fill in the 200 query names):

Public Sub RunMyQueries2()

'use your actual query name in place of "FirstQuery" etc.
DoCmd.OpenQuery ("FirstQuery")
DoCmd.OpenQuery ("SecondQuery")
DoCmd.OpenQuery ("ThirdQuery")
DoCmd.OpenQuery ("FourthQuery")
'etc...

End Sub

Ask if you aren't sure how to use the VBA code. You may also want to suppress the standard Access pop-up messages if these are append queries using : DoCmd.Setwarnings (False) and then to turn them back on at the end use DoCmd.Setwarnings (True).

HTH, Andrew

 

[bbbuffalo]

Thanks Andrew,
The second option makes sense, but I am a total newbie with VBA.

If you have time and don't mind spoonfeeding me the steps, I would greatly appreciate it.

If I am in Access and I have qryFirst, qrySecond, qryThird, and qryFourth, and I want a way to make them run one right after another and create some display or report of the results that I can print all together, how do I start?

Thanks again!

 

[Andrew Fergus]

Hi

Thats a pretty wide question given I'm not sure how far you have got with your database. Lets assume you have a whole bunch of tables full of data, your 200 queries and nothing else.

Assuming your queries are select queries, create a new module using this code - but use your actual query names in place of the ones I provided.

Public Function RunMyQueries()

DoCmd.OpenQuery "qryFirst"
DoCmd.PrintOut acPrintAll
DoCmd.Close acQuery, "qryFirst", acSaveNo
DoCmd.OpenQuery "qrySecond"
DoCmd.PrintOut acPrintAll
DoCmd.Close acQuery, "qrySecond", acSaveNo
'etc

End Function

Save and close the module. Create a new macro, and make the first line this:
RunCode
and set the 'function name' property to RunMyQueries(). Save and close the macro.

Any time you want to run all of the queries just double click the macro you created.

If you are wanting to print out the results of the queries, this code will do it for you. I personally prefer to print reports and not queries - if you want to do that then create the reports, and instead of opening and closing the queries, you would open, print and close the reports within the RunMyQueries function.

HTH, Andrew

 

[Another Primate]

Wow. Thanks, Andrew! I'd googled "how to run multiple queries in Access" and found this post. Super clear instructions. I'd never created a module or a macro in Access... but now I have; took less than five minutes to struggle through thanks to you. And it's going to save my crew time every morning. Thanks again.

 

[Micron]

With 200 queries, I'd consider adding this to my project: Create a table tblQueryNames and enter all of the names if you don't want to loop through the query collection (which is easier). In the code that runs the queries, I'd create a recordset of the query names and loop through them. You can even ID which ones need special handling (such as printing or running via the db.execute method instead of docmd.openquery) by having flag fields in your table. The added benefit is it cuts down the number of lines of code you have to maintain, and each time you create or delete a query, you don't have to find its place in code. Just add, remove or change the name in the table. I can provide the rudimentary code for this if you wish. NOTE; if any of your queries have parameters, they may need addtional coding because MS Access Jet does not handle them in the same way as the database container does.

 

[Another Primate]

Thanks, Micron! I think I'll end up with only a few dozen in my arsenal, but I like the idea anyway because it will be good practice for me.
-Sue