By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,084 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,084 IT Pros & Developers. It's quick & easy.

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

100+
P: 119
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
Share this Question
Share on Google+
7 Replies


100+
P: 119
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
Expert 5K+
P: 8,627
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

100+
P: 119
If you don't mind, that would be very helpful. I'm running Access 2003 if that helps.
Mar 9 '07 #4

ADezii
Expert 5K+
P: 8,627
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
Expert Mod 15k+
P: 31,429
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

100+
P: 119
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
Expert Mod 15k+
P: 31,429
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.