469,366 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

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

119 100+
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.
Mar 9 '07 #1
7 32912
billelev
119 100+
Just to elaborate a bit further...

I can see that
Expand|Select|Wrap|Line Numbers
  1. 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).
Mar 9 '07 #2
ADezii
8,800 Expert 8TB
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.
Mar 9 '07 #3
billelev
119 100+
If you don't mind, that would be very helpful. I'm running Access 2003 if that helps.
Mar 9 '07 #4
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim aob As AccessObject
  2. With CurrentData
  3.    ' "Tables"
  4.    For Each aob In .AllTables
  5.        If aob.IsLoaded Then
  6.            DoCmd.Close acTable, aob.Name, acSaveYes
  7.        End If
  8.    Next aob
  9.  
  10.    ' "Queries"
  11.    For Each aob In .AllQueries
  12.        If aob.IsLoaded Then
  13.            DoCmd.Close acQuery, aob.Name, acSaveYes
  14.        End If
  15.    Next aob
  16. End With
  17.  
  18.  
  19. With CurrentProject
  20.    ' "Forms"
  21.    For Each aob In .AllForms
  22.        If aob.IsLoaded Then
  23.            DoCmd.Close acForm, aob.Name, acSaveYes
  24.        End If
  25.    Next aob
  26.  
  27.    ' "Reports"
  28.    For Each aob In .AllReports
  29.        If aob.IsLoaded Then
  30.            DoCmd.Close acReport, aob.Name, acSaveYes
  31.        End If
  32.    Next aob
  33.  
  34.    ' "Pages"
  35.    For Each aob In .AllDataAccessPages
  36.        If aob.IsLoaded Then
  37.            DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
  38.        End If
  39.    Next aob
  40.  
  41.    ' "Macros"
  42.    For Each aob In .AllMacros
  43.        If aob.IsLoaded Then
  44.            DoCmd.Close acMacro, aob.Name, acSaveYes
  45.        End If
  46.    Next aob
  47.  
  48.    ' "Modules"
  49.    For Each aob In .AllModules
  50.        If aob.IsLoaded Then
  51.            DoCmd.Close acModule, aob.Name, acSaveYes
  52.        End If
  53.    Next aob
  54. End With
Mar 10 '07 #5
NeoPa
32,185 Expert Mod 16PB
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.
Mar 10 '07 #6
billelev
119 100+
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.
Mar 13 '07 #7
NeoPa
32,185 Expert Mod 16PB
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.
Mar 13 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Bryan | last post: by
reply views Thread by markusp1982 | last post: by
2 posts views Thread by Bruno Rodrigues | last post: by
5 posts views Thread by Steve Lloyd | last post: by
reply views Thread by nezoat | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.