loop and close all open tables

[Guest]

Hi,

After I run several queries with a macro, all the tables, or datasheet views
stay open. I have to manually close each one of them. Is there a vba that
loops through all tables and queries in the database and close all the opened
ones?

Thanks,
Carmen

 

[Dirk Goldgar]

Hi,

After I run several queries with a macro, all the tables, or
datasheet views stay open.

Why is that? I'm curious about what your macro is doing, and why it is
opening all these tables and queries if you're not going to interact
with the datasheets. It's possible you're doing something you don't
have to.

I have to manually close each one of
them. Is there a vba that loops through all tables and queries in
the database and close all the opened ones?

This code will do it, if you're running Access 2000 or later:

'----- start of code -----
Dim ao As AccessObject

' Close all open tables
For Each ao In CurrentData.AllTables
With ao
If .IsLoaded Then
DoCmd.Close acTable, ao.Name
End If
End With
Next ao

' Close all open queries
For Each ao In CurrentData.AllQueries
With ao
If .IsLoaded Then
DoCmd.Close acQuery, ao.Name
End If
End With
Next ao
'----- end of code -----