Is it possible to use VBA code to close all open objects (forms, tables etc.)?

[billelev]

Does anyone know if it is possible to close all objects in a database (tables, forms, queries etc.) using VBA code? I have not been able to find anything online to help me so far...

My motivation for doing this is the following:

I have two databases, call them A and B.

- Database A is linked to a table in database B.
- Database B is updated automatically using Windows Scheduler, on the hour.
- If an object in Database A with a dependency on the linked table in B is open when Database B is opened, Database B will return an error. I cannot have this.
- If all dependent objects in Database A are closed when B opens, there is no problem.
- I would like to automate the closure of Database A objects, to occur before Database B opens via Windows Scheduler. I would be able to do this via VBA code, if only I knew how!

Any help would be greatly appreciated.

[billelev]

Just to elaborate a bit further...

I can see that

DoCmd.Close

might be of use, but this requires each object to be explicitly named. I really need to find a way of closing all open objects, regardless of their names (i.e. I won't know which objects are open).

[ADezii]

Does anyone know if it is possible to close all objects in a database (tables, forms, queries etc.) using VBA code? I have not been able to find anything online to help me so far...

My motivation for doing this is the following:

I have two databases, call them A and B.

- Database A is linked to a table in database B.
- Database B is updated automatically using Windows Scheduler, on the hour.
- If an object in Database A with a dependency on the linked table in B is open when Database B is opened, Database B will return an error. I cannot have this.
- If all dependent objects in Database A are closed when B opens, there is no problem.
- I would like to automate the closure of Database A objects, to occur before Database B opens via Windows Scheduler. I would be able to do this via VBA code, if only I knew how!

Any help would be greatly appreciated.

There is a way to systematically check and see if any Access Objects are Open (Forms, Reports, Queries, etc,) and then to close them. This is fairly easy especially in the higher Versions of Access. Let me know if you are still interested and I'll work on the code.

[billelev]

If you don't mind, that would be very helpful. I'm running Access 2003 if that helps.

[ADezii]

If you don't mind, that would be very helpful. I'm running Access 2003 if that helps.

This code should work very well for you:

Dim aob As AccessObject
With CurrentData
   ' "Tables"
   For Each aob In .AllTables
       If aob.IsLoaded Then
           DoCmd.Close acTable, aob.Name, acSaveYes
       End If
   Next aob
 
   ' "Queries"
   For Each aob In .AllQueries
       If aob.IsLoaded Then
           DoCmd.Close acQuery, aob.Name, acSaveYes
       End If
   Next aob
End With
 
 
With CurrentProject
   ' "Forms"
   For Each aob In .AllForms
       If aob.IsLoaded Then
           DoCmd.Close acForm, aob.Name, acSaveYes
       End If
   Next aob
 
   ' "Reports"
   For Each aob In .AllReports
       If aob.IsLoaded Then
           DoCmd.Close acReport, aob.Name, acSaveYes
       End If
   Next aob
 
   ' "Pages"
   For Each aob In .AllDataAccessPages
       If aob.IsLoaded Then
           DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
       End If
   Next aob
 
   ' "Macros"
   For Each aob In .AllMacros
       If aob.IsLoaded Then
           DoCmd.Close acMacro, aob.Name, acSaveYes
       End If
   Next aob
 
   ' "Modules"
   For Each aob In .AllModules
       If aob.IsLoaded Then
           DoCmd.Close acModule, aob.Name, acSaveYes
       End If
   Next aob
End With

[NeoPa]

Does anyone know if it is possible to close all objects in a database (tables, forms, queries etc.) using VBA code? I have not been able to find anything online to help me so far...

My motivation for doing this is the following:

I have two databases, call them A and B.

- Database A is linked to a table in database B.
- Database B is updated automatically using Windows Scheduler, on the hour.
- If an object in Database A with a dependency on the linked table in B is open when Database B is opened, Database B will return an error. I cannot have this.
- If all dependent objects in Database A are closed when B opens, there is no problem.
- I would like to automate the closure of Database A objects, to occur before Database B opens via Windows Scheduler. I would be able to do this via VBA code, if only I knew how!

Any help would be greatly appreciated.

If you have a scheduled task running on database B and this depends on no-one having any objects of database A (that refer to tables in database B) open, then you need to close database A objects from all sessions running anywhere and by anyone. This is not as simple as you seem to think. Closing them in your session would be pointless (There probably aren't any open in your session anyway.) as other sessions could be open. It is not possible programatically (and would be dangerous) to close all these objects in the other sessions.

[billelev]

That isn't actually an issue as there will probably only ever be one user, and all potential users sit opposite each other. Closing all of the objects automatically (even if only one user) is useful as it removes the need to remember to close the objects on the hour every hour.

[NeoPa]

It is not possible programatically (and would be dangerous) to close all these objects in the other sessions.

I expect the fact that it can't be done may be a problem though.