By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

How do I close all open query results (tables)?

P: 4
I have written code to close all open reports, or close all forms (except a couple), using code similar to the following:

While Application.Reports.Count > 0
DoCmd.Close acReport, Application.Reports(0).Name, acSavePrompt
Wend

I can’t figure out how to do the same thing with the half-dozen or so small query windows (tables) that may remain open when I finish a particular task. I have to click the [x] close button on the Title bar of each one.

TIA

-BhanteU
Jun 28 '10 #1

✓ answered by ADezii

@BhanteU
Access 97 is a horse of a different color, and none of the previously mentioned approaches will work. The following, 'Generic' Code template should work for you:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim intNumOfQueries As Integer
  3. Dim intQCounter As Integer
  4.  
  5. intNumOfQueries = CurrentDb.QueryDefs.Count
  6.  
  7. For intQCounter = 0 To intNumOfQueries - 1
  8.   DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
  9. Next

Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,701
@BhanteU
Expand|Select|Wrap|Line Numbers
  1. Dim aob As AccessObject
  2.  
  3. With CurrentData
  4.   For Each aob In .AllQueries
  5.     If aob.IsLoaded Then
  6.       DoCmd.Close acQuery, aob.Name, acSaveYes
  7.     End If
  8.   Next
  9. End With
Jun 28 '10 #2

P: 4
Thank you.

Access97 doesn’t like the AccessObject data type; it returns a “User-defined type not defined” error at compile time. I’m running Access 97.

When I enter the keyword accesstype (in small letters), Access 97 automatically changes the case, indicating that it recognizes the string.

Substituting AcObjectType doesn’t work, either.

Looks like some kind of undocumented “feature”.

BTW, how did you get that code snippet into the reply window?
Jun 29 '10 #3

FishVal
Expert 2.5K+
P: 2,653
@BhanteU
You can determine the type of AllQueries collection member using either of the following two methods:
  • Open Object Browser window, find "AllQueries" class, check the type returned by "Item" property of this class.
  • In Immediate pane run the following command
    Expand|Select|Wrap|Line Numbers
    1. ? TypeName(CurrentData.AllQueries(0))

Regards,
Fish
Jun 29 '10 #4

ADezii
Expert 5K+
P: 8,701
@BhanteU
Access 97 is a horse of a different color, and none of the previously mentioned approaches will work. The following, 'Generic' Code template should work for you:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim intNumOfQueries As Integer
  3. Dim intQCounter As Integer
  4.  
  5. intNumOfQueries = CurrentDb.QueryDefs.Count
  6.  
  7. For intQCounter = 0 To intNumOfQueries - 1
  8.   DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
  9. Next
Jun 29 '10 #5

P: 4
Thank you, ADezii!

Access Help was not helpful in wading through the quagmire of objects, methods, and properties. I tried many commands like the one you supplied:

CurrentDb.QueryDefs(intQCounter).Name

but I never could get it right. Yous works swell!

Thank you so much!

(And still I would like to know how one gets that nicely-formatted code window into a reply.)

-Ven. Upatissa
Jun 29 '10 #6

ADezii
Expert 5K+
P: 8,701
Select the Code Segment with the Mouse, then click the Hash (#) on the Message Window Toolbar. You will now see the Code surrounded by Code Tags, and it will be formatted accordingly.
Jun 29 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
An alternative that doesn't need to maintain the counter would be :
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. For Each qdf In CurrentDb.QueryDefs
  4.     Call DoCmd.Close(acQuery, qdf.Name)
  5. Next qdf
Unfortunately, neither cycles through only those queries that are currently open. I wasn't able to find anything that indicated that.
Jun 29 '10 #8

ADezii
Expert 5K+
P: 8,701
@NeoPa
Hello NeoPa. I don't think that the For...Each Construct will work in Access 97, but I could be wrong.
Jun 29 '10 #9

P: 4
My utmost thanks to Adezi and NeoPa. I can report that both routines work in Access 97. My apologies for not revealing earlier that I was using antiquated Access 97.

Several years ago I wrote a medium-sized management system in MS Access. I’ve been doing periodic maintenance and tweaking ever since. The DBMS handles the membership records for a not-for-profit institution. At present it contains around 7,000 member records and 100,000 transactions. The organization is not keen to spend any money at all, and I have advised them to stick with Win 98 SE and Access 97 for the membership clerk. Why fix it if it ain’t broke?

So again, my thanks to everyone.

Sadhu!

(please mark this issue as closed.)
Jun 29 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.