Stop Interim Tables from Opening when running multiple queries in MS Access

[Bobbit]

Hi All,

I've created a command button to run multiple queries in Access. Everything run great except that all interim queries(Select Queries) open up and fills up my screen. I just close them all manually when it is done. Is there a way to omit these interim tables from Opening? Is there a code that I can add on so it will run all interim tables but omit them from opening? Please Help.....and thank you so much.

Bobitt

[NeoPa]

Hi Bobbit and Welcome to Bytes!

Unfortunately, without more information from you we have no way of understanding what you're asking. Interim tables can only mean something when you know about the progress they are interim within. Frankly, I very much doubt they are interim at all as I can think of no way that these would be opened unless called to do so explicitly.

Perhaps if you posted the code that you are asking about would could see what is actually going wrong. You must also explain which tables are displayed that you think shouldn't be any any relationships between any called that might make you consider one as an interim.

[Bobbit]

Thanks NeoPa!! I will re-do the thread and include the code. Thank you for your quick reply....really appreciate your help....

Option Compare Database
Private Sub Run_All_Cap_Error_Queries_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "(1) Mod Data"
DoCmd.OpenQuery "(2) MonthDLQComp"
DoCmd.OpenQuery "(3) MonthlyTable"
DoCmd.OpenQuery "(4) BaseComp"
DoCmd.OpenQuery "(5) RATE X"
DoCmd.OpenQuery "(6) RATE MAX"
DoCmd.OpenQuery "(7) BEGRATE"
DoCmd.OpenQuery "(8) IRPOP"
DoCmd.OpenQuery "(9) IR LOGIC"
DoCmd.OpenQuery "(9x) IR LOGIC TABLE"
DoCmd.OpenQuery "(9y) PMT LOGIC TEST"
DoCmd.OpenQuery "X10 INTAMT Query"
DoCmd.OpenQuery "X11 CALC INTAMT"
DoCmd.OpenQuery "X12 CAP ERROR LOGIC"
DoCmd.OpenForm "CAP ERROR RESULT"
DoCmd.SetWarnings True
End Sub

------
Most Queries above are all (Nine)Select Queries except for the following Make Table Queries (3),(4),(9x),(x 10), & (X12)

Is there a way I can add a command line to close all Select Tables before opening the Cap Error Result Form? or am I stuck on simply to close all the tables manually unless I change all the Select Queries to Make Table queries?

[Rabbit]

If it's just a select query, you don't need to open them.

[NeoPa]

You don't quite provide all of the information I suggested. What you do provide is very helpful though.

My confusion at this time revolves around why you open the queries that you apparently don't want to open. If they weren't in the list in your code they wouldn't be opened. Is it as simple as that?

[Bobbit]

Hi All,

Thank you so much for all your quick reply on this. I actually do want those Select Queries to open since they are required to prep the Make Tables Queries. Is there some Command Code to close them all before opening the Form(Cap Error Result)?? I am trying to get away from manually Closing all the Select Queries that opened during the whole process.... Again Thank you so much...


Bo.Bitt

[Bobbit]

Hey "A.Dezii" .....Thank you so much

The code works!! All of the Select Queries opened and closed. I see only 2 exceptions (1)Crosstab Query and (1) Union Query. Is there a way to include these 2 as well? Please advice and thank you so much for your help.

Thank you All for all the quick replies.
You guys are AWESOME!

[NeoPa]

Bobbit:
I actually do want those Select Queries to open since they are required to prep the Make Tables Queries.

That is just not correct. They may be required and an integral part of some other action (Make Table; Update; Insert) queries, but they don't need to be opened separately (for display) for the action query to be effective. The action queries themselves automatically use the data from whatever queries they're built from. ADezii's code (since deleted as it was part of a new thread on the same subject which was deleted - Please don't ever do that again) very probably works, but it doesn't solve your problem, which is a basic misunderstandin g of how these things work.

I suspect the difficulty in replying generally to such a simple question has been the difficulty of appreciating exactly what you might be thinking that would raise such a question in the first place. I suggest you are not looking to close the queries automatically at all (although it never hurts to know how to which ADezii's code can help you with), but rather to open only those queries that you need in the first place. An understanding of how that all works is necessary to appreciate which they should be.

[Rabbit]

In short, an action query that references a select query does not need that select query to be open. It will work without opening the select query.

[Bobbit]

Thank you. I stand and corrected. So I didnt need to include the select queries to open. I didn't know that.
As for A.Denzii code or reply, I didn't delete it. I couldn't open the link that was provided, but the code was included below: Here is the message.
Again, I want to thank you for all your Help....


This thread is located at:

http://bytes.com/topic/access/answers/913349-how-add-close-command-all-select-tables/new

Here is the message that has just been posted:
***************
The following Code should Close all SELECT Queries only, I'll leave it up tp you to actually test it:

Dim qdf As DAO.QueryDef

For Each qdf In CurrentDb.QueryDefs
  If qdf.Type = dbQSelect Then      'SELECT Queries only, Close them
    DoCmd.Close acQuery, qdf.name
  End If
Next

---------

***************