Read all open objects

[Guest]

I'd like to create an autokey that when pressed, will close whatever query is
opened.

How do I read which objects are open?

Crystal

 

[Marshall Barton]

I'd like to create an autokey that when pressed, will close whatever query is
opened.

How do I read which objects are open?

Note that an "open query" is a query being deisplayed in
datasheet view. This is something that should not be done
in a runnung application.

What that caveat, you can enumerate them by using a little
VBA code:

Dim db As Database
Dim qry As AccessObject
Set db = CurrentDb()
For Each qry In db.AllQueries
If qry.IsLoaded _
Then DoCmd.Close acQuery, qry.Name
Next qry
Set db = Nothing

 

[Guest]

Still confused.

The IsLoaded function is designed for forms. How would I adapt it to read
queries?

Additionally, "AllQueries" is not an option. Will QueryDefs work?

 

[Marshall Barton]

Still confused.

The IsLoaded function is designed for forms. How would I adapt it to read
queries?

Additionally, "AllQueries" is not an option. Will QueryDefs work?

Well, I was confused too and used the wrong objects. I
would think I would know better than to trust my own air
code, sorry about that. Let's give this a try:

Dim db As Object
Dim qry As AccessObject
Set db = Application.CurrentData
For Each qry In db.AllQueries
If qry.IsLoaded _
Then DoCmd.Close acQuery, qry.Name
Next qry
Set db = Nothing

Note that IsLoaded applies to all AccessObjects, not just
forms. The QueryDef object does not have an equivalent to
the IsLoaded property and there is no Queries collection in
the Application object.

 

[Guest]

There's got to be something I'm missing here. Is there a particular
reference I need to add in order to use "AccessObject" as a datatype and
"db.AllQueries"? Also, should "Set db = Application.CurrentData read "Set db
= Application.CurrentDb"?

This code won't actually run for me.

 

[Douglas J. Steele]

What version of Access are you using? That code should be fine in all
versions since Access 2000: it's essentially the same code as in
http://msdn.microsoft.com/library/en-us/vbaac11/html/accolAllQueries_HV05251615.asp

If you're still using Access 97 or earlier, try:

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim intLoop As Integer

Set db = CurrentDb()
For intLoop = (db.QueryDefs.Count - 1) To 0 Step -1
Set qry = db.QueryDefs(intLoop)
If Syscmd(acSysCmdGetObjectState , acQuery, qdf.Name) > 0 Then
DoCmd.Close acQuery,. qdf.Name
End If
Next intLoop
Set db = Nothing

 

[Guest]

EXCELLENT!

Thank you. I totally forgot to mention that at my company, we rock the
Access 97. We're only slightly behind the times.