loop and close all open tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carmen - 18 Nov 2006 23:32 GMT

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

Reply to this Message

Dirk Goldgar - 19 Nov 2006 01:15 GMT

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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message