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

[BhanteU]

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

[ADezii]

@BhanteU

Dim aob As AccessObject
 
With CurrentData
  For Each aob In .AllQueries
    If aob.IsLoaded Then
      DoCmd.Close acQuery, aob.Name, acSaveYes
    End If
  Next
End With

[BhanteU]

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?

[FishVal]

@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

  ? TypeName(CurrentData.AllQueries(0))

Regards,
Fish

[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:

On Error Resume Next
Dim intNumOfQueries As Integer
Dim intQCounter As Integer
 
intNumOfQueries = CurrentDb.QueryDefs.Count
 
For intQCounter = 0 To intNumOfQueries - 1
  DoCmd.Close acQuery, CurrentDb.QueryDefs(intQCounter).Name, acSaveYes
Next

[BhanteU]

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

[ADezii]

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.

[NeoPa]

An alternative that doesn't need to maintain the counter would be :

Dim qdf As DAO.QueryDef
 
For Each qdf In CurrentDb.QueryDefs
    Call DoCmd.Close(acQuery, qdf.Name)
Next qdf

Unfortunately, neither cycles through only those queries that are currently open. I wasn't able to find anything that indicated that.

[ADezii]

@NeoPa
Hello NeoPa. I don't think that the For...Each Construct will work in Access 97, but I could be wrong.

[BhanteU]

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.)