Once again, a continuation of my recent posts:
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).
…
In this post, I’d like to present another function which will close all the currently open Macros within the current database.
The Code
'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenMacros
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Close all the currently open Macros in the database
' 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: None required
'
' Usage:
' ~~~~~~
' ? CloseAllOpenMacros
' Returns -> True => Closed all Macros successfully
' False => A problem occurred
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2015-02-17 Initial Release
' 2 2023-02-09 Update variable naming, Error handler, copyright
'---------------------------------------------------------------------------------------
Function CloseAllOpenMacros() As Boolean
On Error GoTo Error_Handler
Dim oMcrs As Object
Dim oMcr As Access.AccessObject
Set oMcrs = CurrentProject.AllMacros
For Each oMcr In oMcrs 'Loop all the queries
If oMcr.IsLoaded = True Then 'check if it is open
DoCmd.Close acMacro, oMcr.Name, acSaveNo
End If
Next oMcr
CloseAllOpenMacros = True
Error_Handler_Exit:
On Error Resume Next
Set oMcr = Nothing
Set oMcr = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: CloseAllOpenMacros" & 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
Once again, the code is very straight forward. It simply loops through all the database tables (CurrentData.AllMacros), checks to see if the table is currently loaded (IsLoaded), and if so, closes it (DoCmd.Close).
Usage Example(s)
Then to use the function we simply do:
Call CloseAllOpenMacros
Or
If CloseAllOpenMacros = True Then
'...
End If
Useful Resources
AllMacros object (Access)
The AllMacros collection contains an AccessObject for each macro in the CurrentProject or CodeProject object.