So after posting about closing individual objects:
Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!
Access – VBA – Close All Open Queries
Continuing down the same path as my previous post: I thought I’d share a simple function to close any and all open queries. The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).
MS Access - VBA - Close All Open Forms
In some cases it may be useful to close all the forms within a database. Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.
Access – VBA – Close All Open Reports
Similar to my previous couple of posts: I thought I’d present a function to close all the open reports within a database. The code is slightly simpler that the 2 previous posts as we can use the Reports property which returns a list of only the open reports. Thus we don’t need to validate whether…
Access – VBA – Close All Open Macros
Once again, a continuation of my recent posts: … In this post, I’d like to present another function which will close all the currently open Macros within the current database.
I thought it simply made sense to create a single procedure that did it all.
The Code
'---------------------------------------------------------------------------------------
' Procedure : CloseAllObjects
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Close all the database object of the specified type(s)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bCloseTables : Optional - True => Close all tables
' False => Do not close open tables
' bCloseQueries : Optional - True => Close all queries
' False => Do not close open queries
' bCloseForms : Optional - True => Close all forms
' False => Do not close open forms
' bCloseReports : Optional - True => Close all reports
' False => Do not close open reports
' bCloseMacros : Optional - True => Close all macros
' False => Do not close open macros
'
' Usage:
' ~~~~~~
' Close all open objects
' ? CloseAllObjects
' Returns -> True or False
'
' Close all open objects except for queries
' ? CloseAllObjects(, False)
' Returns -> True or False
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2023-02-13 Initial Release
' 3 2023-03-13 Changed approach as For each frm in Forms skips
' objects, as does For each rpt in Reports
'---------------------------------------------------------------------------------------
Function CloseAllObjects(Optional bCloseTables As Boolean = True, _
Optional bCloseQueries As Boolean = True, _
Optional bCloseForms As Boolean = True, _
Optional bCloseReports As Boolean = True, _
Optional bCloseMacros As Boolean = True) As Boolean
On Error GoTo Error_Handler
Dim oO As Object
Dim oAO As Access.AccessObject
Dim i As Long
If bCloseTables = True Then
Set oO = CurrentData.AllTables
For Each oAO In oO
If oAO.IsLoaded = True Then
DoCmd.Close acTable, oAO.Name, acSaveNo
End If
Next oAO
End If
If bCloseQueries = True Then
Set oO = CurrentData.AllQueries
For Each oAO In oO
If oAO.IsLoaded = True Then
DoCmd.Close acQuery, oAO.Name, acSaveNo
End If
Next oAO
End If
If bCloseForms = True Then
For i = Application.Forms.Count - 1 To 0 Step -1
DoCmd.Close acForm, Forms(i).Name, acSaveNo
Next i
End If
If bCloseReports = True Then
For i = Application.Reports.Count - 1 To 0 Step -1
DoCmd.Close acReport, Reports(i).Name, acSaveNo
Next i
End If
If bCloseMacros = True Then
Set oO = CurrentProject.AllMacros
For Each oAO In oO
If oAO.IsLoaded = True Then
DoCmd.Close acMacro, oAO.Name, acSaveNo
End If
Next oAO
End If
CloseAllObjects = True
Error_Handler_Exit:
On Error Resume Next
Set oAO = Nothing
Set oO = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: CloseAllObjects" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
each object type is now controlled by a separate input argument, so you can select which objects to close, or not, by simply entering True/False for each!
So a single routine to rule them all.
Usage Example(s)
Close All Open Objects
To close all the open objects, you’d simply do:
? CloseAllObjects
Or
Call CloseAllObjects
Or
If CloseAllObjects = True Then
'...
End if
Close All Open Tables Only
To close all the open objects, you’d simply do:
? CloseAllObjects(, False, False, False, False)
Or
Call CloseAllObjects(, False, False, False, False)
Or
If CloseAllObjects(, False, False, False, False) = True Then
'...
End if
Close All Open Open Objects Except For Forms
To close all the open objects, you’d simply do:
? CloseAllObjects(, , False)
Or
Call CloseAllObjects(, , False)
Or
If CloseAllObjects(, , False) = True Then
'...
End if
so on and so forth!
This is the one I was waiting on! WIth all the other snippets it would have been easy to simply cobble this together but something told me this was at the end of the rainbow and since I am lazy, I figured I would wait. Glad I was not wrong and even more glad you take the time to share your knowledge…