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.
7 34979
Just to elaborate a bit further...
I can see that
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).
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.
If you don't mind, that would be very helpful. I'm running Access 2003 if that helps.
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 32,556
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.
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 32,556
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bryan |
last post by:
Can anyone give me some pointers please on where to look next in
solving the problem I'm getting with running a Perl program (my first
of any real complexity) from the command line in WindowsXP?
...
|
by: markusp1982 |
last post by:
Hello NG,
I want to close open files which are locked by other users. I like to
update an file. I dont want to delete the share file/folder. In the
Computer Mangagement you can remove(cut) all...
|
by: Bruno Rodrigues |
last post by:
Hey, it's me again.
Is there a way to, when closing a form, close also all forms opened inside
this one? I'm not talking about MDI.
Let's say I create and open three forms in Form1. When I close...
|
by: Steve Lloyd |
last post by:
Hi,
I have a multi form windows application but when i open new forms they do
not load up "crisply", they open bit by bit as the items are drawn.
What is the best way to open the form so that...
|
by: nezoat |
last post by:
Here is some working code to open the specified TCP port on the gateway
nat device or firewall, and forward it to the calling machine. Great
for p2p apps. The newsgroups are such a great...
|
by: News East |
last post by:
Currently I must manually complete the following commands to set a new value
for the "open objects" and "open indexes". I need a way script this process
in a batch file.
COMMANDS RAN: This is...
|
by: Mathew Butler |
last post by:
I'm investigating an issue I have when pulling data back from MS access I'm retrieving all rows from a column in a table of 5000 rows - accessing a column of type "memo" ( can be 65353 character...
|
by: bonnie.tangyn |
last post by:
Hello all
Would it be possible to store javascript document.forms.value to ASP
session as global variable? If it is not possible, how can I pass the
javascript document.forms.value to...
|
by: D Giles |
last post by:
Have found many solutions on this forum to get to this point so finally registered.
I have a form which should load 17 forms.
Private Sub Form_Load()
On Error GoTo Err_Form_Load
DoCmd.Minimize...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| | |