Have you ever needed to close any and all open tables? Ensure that no tables are still open?

It is remarkably easy to do!
 

The Code

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenTables
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Tables 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:
' ~~~~~~
' ? CloseAllOpenTables
'   Returns -> True     => Closed all Tables 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 CloseAllOpenTables() As Boolean
    On Error GoTo Error_Handler
    Dim oTbls                 As Object
    Dim oTbl                  As Access.AccessObject

    Set oTbls = CurrentData.AllTables

    For Each oTbl In oTbls    'Loop all the tables
        If oTbl.IsLoaded = True Then 'check if it is open
            DoCmd.Close acTable, oTbl.Name, acSaveNo
        End If
    Next oTbl
    
    CloseAllOpenTables = True

Error_Handler_Exit:
    On Error Resume Next
    Set oTbl = Nothing
    Set oTbls = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CloseAllOpenTables" & 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

The code is very straight forward. It simply loops through all the database tables (CurrentData.AllTables), 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 CloseAllOpenTables

Or

If CloseAllOpenTables = True Then
    '...
End If

Useful Resources

AllTables object (Access)

The AllTables collection contains an AccessObject for each table in the CurrentData or CodeData object.

AccessObject.IsLoaded property (Access)

Use the IsLoaded property to determine if an AccessObject object is currently loaded. Read-only Boolean.

DoCmd.Close method (Access)

The Close method carries out the Close action in Visual Basic.