MS Access – Listing of Database Objects (Tables, Queries, Forms, Reports, …)

It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’.

The basic query SQL statment is as follows:

SELECT MsysObjects.Name AS [List Of Tables]
FROM MsysObjects
WHERE (((MsysObjects.Name Not Like "~*") And (MsysObjects.Name Not Like "MSys*")) 
	AND (MsysObjects.Type=1))
ORDER BY MsysObjects.Name;

You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various common values of interest that can be used to return the various objects available in Access:

Object Type Value
Tables (Local) 1
Tables (Linked using ODBC) 4
Tables (Linked) 6
Queries 5
Forms -32768
Reports -32764
Macros -32766
Modules -32761

 

A Simple VBA Solution

Knowing the above, we could then create a function similar to

Enum AccessObjectType
    Access_Tables_Local = 1
    Access_Tables_Linked_ODBC = 4
    Access_Tables_Linked = 6
    Access_Queries = 5
    Access_Forms = -32768
    Access_Reports = -32764
    Access_Macros = -32766
    Access_Modules = -32761
End Enum

Function ListObjects(lObjectType As AccessObjectType)
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String

    sSQL = "SELECT MsysObjects.Name AS [ObjectName]" & vbCrLf & _
           " FROM MsysObjects" & vbCrLf & _
           " WHERE (((MsysObjects.Name Not Like '~*') And (MsysObjects.Name Not Like 'MSys*'))" & vbCrLf & _
           "     AND (MsysObjects.Type=" & lObjectType & "))" & vbCrLf & _
           " ORDER BY MsysObjects.Name;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                Debug.Print ![ObjectName]
                .MoveNext
            Loop
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

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

and then simply call it like

ListObjects Tables_Local

or

ListObjects Forms

You may also be interested in the following VBA approaches:
List the Tables in a Database
List of Database Queries

More Object Types You Say

Above I gave the listing of the common object type, those that 99% of people might be interested in, that said, for those of you trying to understand it all, below is a more complete listing

Object Type Value
Tables (Local) 1
Access Object – Database 2
Access Object – Container 3
Tables (Linked using ODBC) 4
Queries 5
Tables (Linked) 6
SubDataSheet, Relationship? 8
Constraint 9
Data Access Page -32756
Database Document -32757
User -32758
Forms -32768
Reports -32764
Macros -32766
Modules -32761

Flags

You may also notice that in the MsysObjects table there is a Flags field. This field provides an Attribute value which represents extra information about the object itself. Below is what I could find from various sources or identify myself as what these values meant.

‘Flags’ Values Description
-2147483648 MSys Table (w/ read permission)
-2147352566 MSys Table …
-2147287040 MSys Table …
-2146828288 MSys Table …
0 Select query (visible) or Standard Table
2 MSys Table (w/o read permission)
3 Deleted or temp ( prefixed with ~)
8 Select query (hidden)
10 MSys Table …
16 Crosstab query (visible)
24 Crosstab query (hidden)
32 Delete query (visible)
40 Delete query (hidden)
48 Update query (visible)
56 Update query (hidden)
64 Append query (visible)
72 Append query (hidden)
80 Make table query (visible)
88 Make table query (hidden)
96 Data Definition query (visible)
104 Data Definition query (hidden)
112 Pass through query (visible)
120 Pass through query (hidden)
128 Union query (visible)
136 Union query (hidden)
262154 MSys Table …
2097152 Linked Table
2359296

 

From Single Argument to ParamArray: Making the Function More Flexible

The original version of the function accepted a single AccessObjectType enum value, which worked well when only one kind of object needed to be listed at a time. Once the requirement expanded to include scenarios such as listing all tables or both forms and reports, the interface became unnecessarily restrictive. Addressing this by calling the function multiple times or creating combined enum values adds duplication, obscures intent without improving the core logic and impacts overall performance.

This is why it made sense to Refactor the function to use ParamArray which shifts the design toward a much more flexible usage pattern. By allowing one or more enum values and converting them into an IN clause, the function becomes more flexible and expressive while keeping the enum simple. The internal changes are small, but the calling code becomes clearer, easier to extend, and better aligned with real usage patterns.

Thus to make the original function more versatile, we can massage it slightly into:

Enum AccessObjectType
    Access_Tables_Local = 1
    Access_Tables_Linked_ODBC = 4
    Access_Tables_Linked = 6
    Access_Queries = 5
    Access_Forms = -32768
    Access_Reports = -32764
    Access_Macros = -32766
    Access_Modules = -32761
End Enum


Public Function ListObjects(ParamArray lObjectTypes() As Variant)
On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim sTypes                As String
    Dim i                     As Long

    ' Build comma-separated list for SQL IN (...)
    For i = LBound(lObjectTypes) To UBound(lObjectTypes)
        sTypes = sTypes & lObjectTypes(i) & ","
    Next i
    sTypes = Left(sTypes, Len(sTypes) - 1)    ' trim trailing comma

    ' Build the SQL Statement
    sSQL = "SELECT MsysObjects.Type, MsysObjects.Name AS [ObjectName]" & vbCrLf & _
           "FROM MsysObjects" & vbCrLf & _
           "WHERE MsysObjects.Name Not Like '~*'" & vbCrLf & _
           "  AND MsysObjects.Name Not Like 'MSys*'" & vbCrLf & _
           "  AND MsysObjects.Name Not Like 'f_*_ImageData'" & vbCrLf & _
           "  AND MsysObjects.Name Not Like 'f_*_Data'" & vbCrLf & _
           "  AND MsysObjects.Type IN (" & sTypes & ")" & vbCrLf & _
           "ORDER BY MsysObjects.Type, MsysObjects.Name;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    Do While Not rs.EOF
        Debug.Print AccessObjectTypeName(rs!type), rs!ObjectName
        rs.MoveNext
    Loop

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

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


Public Function AccessObjectTypeName(ByVal lType As Long) As String
    Select Case lType
        Case Access_Tables_Local:          AccessObjectTypeName = "Local Table"
        Case Access_Tables_Linked:         AccessObjectTypeName = "Linked Table"
        Case Access_Tables_Linked_ODBC:    AccessObjectTypeName = "ODBC Linked Table"
        Case Access_Queries:               AccessObjectTypeName = "Query"
        Case Access_Forms:                 AccessObjectTypeName = "Form"
        Case Access_Reports:               AccessObjectTypeName = "Report"
        Case Access_Macros:                AccessObjectTypeName = "Macro"
        Case Access_Modules:               AccessObjectTypeName = "Module"
        Case Else:                         AccessObjectTypeName = "Unknown (" & lType & ")"
    End Select
End Function

Also notice the use of the AccessObjectTypeName() helper function to output the plain English name of the object types.

So now, we can output multiple object types in a single call by doing something along the lines of:

Call ListObjects(Access_Queries, Access_Tables_Local, Access_Modules)

Which outputs something similar to:

Module        mod_Files
Module        mod_ListObjects
Module        mod_Ribbon
Module        mod_RibbonCallbacks
Module        Module1
Module        Module2
Module        Module3
Module        Module4
Local Table   RibbonImages
Local Table   tblBinary
Local Table   USysRibbons
Query         qry_Admin_USysRibbons

and you could easily change the output from a Debug.Print to an array, collection, … to pass the results more easily to a calling procedure.

Outputting the Results as a Collection

For instance, below is an example of outputting the results as a Collection:

Function ListObjects(ParamArray lObjectTypes() As Variant) As Collection
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim sTypes                As String
    Dim i                     As Long
    Dim col                   As New Collection
    Dim obj                   As Object    ' Is a Dictionary

    ' Build comma-separated list for SQL IN (...)
    For i = LBound(lObjectTypes) To UBound(lObjectTypes)
        sTypes = sTypes & lObjectTypes(i) & ","
    Next i
    sTypes = Left(sTypes, Len(sTypes) - 1)    ' remove trailing comma

    ' Build the SQL Statement
    sSQL = "SELECT MsysObjects.Type, MsysObjects.Name AS [ObjectName] " & _
           "FROM MsysObjects " & _
           "WHERE MsysObjects.Name Not Like '~*' " & _
           "  AND MsysObjects.Name Not Like 'MSys*' " & _
           "  AND MsysObjects.Name Not Like 'f_*_ImageData'" & vbCrLf & _
           "  AND MsysObjects.Name Not Like 'f_*_Data'" & vbCrLf & _
           "AND MsysObjects.Type IN (" & sTypes & ") " & _
           "ORDER BY MsysObjects.Type, MsysObjects.Name;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

    ' Loop through results and store in collection of dictionaries
    Do While Not rs.EOF
        Set obj = CreateObject("Scripting.Dictionary")
        obj("Type") = rs!type
        obj("TypeName") = AccessObjectTypeName(rs!type)
        obj("Name") = rs!ObjectName
        col.Add obj
        rs.MoveNext
    Loop

    ' Pass the collection back as the function output
    Set ListObjects = col

Error_Handler_Exit:
    On Error Resume Next
    Set col = Nothing
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

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

which we could test out by simply doing:

Sub ListObjects_test()
    Dim item                  As Object
    Dim results               As Collection

    Set results = ListObjects(Access_Queries, Access_Forms, Access_Tables_Local)

    For Each item In results
        Debug.Print "Type: " & item("Type"), "TypeName: " & item("TypeName"), "Name: " & item("Name")
    Next
    
    Set results = Nothing
End Sub

Outputting the Results as a Recordset

Here’s another cool options, output the results as a DAO.Recordset! Then you can use all the typical techniques to work with it.

Function ListObjects(ParamArray lObjectTypes() As Variant) As DAO.Recordset
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim sTypes                As String
    Dim i                     As Long

    ' Build comma-separated list for SQL IN (...)
    For i = LBound(lObjectTypes) To UBound(lObjectTypes)
        sTypes = sTypes & lObjectTypes(i) & ","
    Next i
    sTypes = Left(sTypes, Len(sTypes) - 1)    ' remove trailing comma

    ' Construct SQL
    sSQL = "SELECT MsysObjects.Type, MsysObjects.Name AS [ObjectName]" & vbCrLf & _
           "FROM MsysObjects" & vbCrLf & _
           "WHERE MsysObjects.Name Not Like '~*' " & _
           "  AND MsysObjects.Name Not Like 'MSys*' " & _
           "  AND MsysObjects.Name Not Like 'f_*_ImageData'" & vbCrLf & _
           "  AND MsysObjects.Name Not Like 'f_*_Data'" & vbCrLf & _
           "  AND MsysObjects.Type IN (" & sTypes & ")" & vbCrLf & _
           "ORDER BY MsysObjects.Type, MsysObjects.Name;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

    ' Return the recordset to the caller
    Set ListObjects = rs

Error_Handler_Exit:
    On Error Resume Next
    ' ***** Do not close rs; caller is responsible
    'rs.Close
    'Set rs = Nothing
    Set db = Nothing
    Exit Function

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

which we can then use by doing:

ub ListObjects_test()
    Dim rs                    As DAO.Recordset

    Set rs = ListObjects(Access_Queries, Access_Tables_Local, Access_Reports)

    Do While Not rs.EOF
        Debug.Print "Type: " & rs!type, "TypeName: " & AccessObjectTypeName(rs!type), "Name: " & rs!ObjectName
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
End Sub

So as you can see there are plenty of different ways that we could approach this and there are even more. The choice is yours.

37 responses on “MS Access – Listing of Database Objects (Tables, Queries, Forms, Reports, …)”

  2. Virgil Mckirgan

    I harmonise with your conclusions and will thirstily look forward to your next updates. Just saying thanks will not just be enough, for the exceptional clarity in your writing. I will directly grab your rss feed to stay privy of any updates. Solid work and much success in your business endeavors!

  8. Mike Carter

    Is it possible to develop this further to get for instance a text representation of a form definition.
    Something like the Object List Report for an Oracle form.
    This would be a useful tool for easily assessing the differences between two versions of the same form.

    1. Daniel Pineault Post author

      You could try something like

      SELECT MsysObjects.Name AS [List OF TABLES], MsysObjects.Type
FROM MsysObjects
WHERE ((MsysObjects.Name NOT LIKE "~*") AND (MsysObjects.Name NOT LIKE "MSys*")) 
ORDER BY MsysObjects.Name;

      You could take things further (I started the work to illustrate the principle, but there are more values to enter) and do something like

      SELECT MsysObjects.Name AS [List OF TABLES], MsysObjects.Type, Switch(MsysObjects.Type=1,"Local Table",MsysObjects.Type=4,"Linked ODBC Table",MsysObjects.Type=5,"Query",MsysObjects.Type=6,"Linked Table",MsysObjects.Type=-32768,"Form",MsysObjects.Type=-32764,"Report",MsysObjects.Type=-32766,"Macro",MsysObjects.Type=-32761,"VBA Module") AS [Object Type]
FROM MsysObjects
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*"))
ORDER BY Switch(MsysObjects.Type=1,"Local Table",MsysObjects.Type=4,"Linked ODBC Table",MsysObjects.Type=5,"Query",MsysObjects.Type=6,"Linked Table",MsysObjects.Type=-32768,"Form",MsysObjects.Type=-32764,"Report",MsysObjects.Type=-32766,"Macro",MsysObjects.Type=-32761,"VBA Module"), MsysObjects.Type, MsysObjects.Name;
  11. jaffer almoosawi

    Hi Daniel

    Thank you for your tips, done in a very clean and easy to understand.
    I copied the above Function in a module, and from immediate view got the lists I needed.

    After doing design changes (nothing to do with the Function), and compiling the DB every once in a while, I started getting the following error:
    Qualifier must be collection
    the line which was highlighted was: Forms!frm_Print!lst_Items , with !frm_Print highlighted in blue.
    This line among others, were working correctly.
    Searching the internet didn’t reveal any answers nor clues either.
    After thinking of the changes I made to the DB, I thought of deleting your Function, and Boom, everything worked fine !!

    Maybe the error was confused with your declaring the word “Forms” in Enum ObjectType !!

    1. Daniel Pineault Post author

      I am unable to replicate the issue at my end. If you believe it to be an issue with the Enum, simply change it to something like

      Enum ObjectType
    Access_Tables_Local = 1
    Access_Tables_Linked_ODBC = 4
    Access_Tables_Linked = 6
    Access_Queries = 5
    Access_Forms = -32768
    Access_Reports = -32764
    Access_Macros = -32766
    Access_Modules = -32761
End Enum

      That said, Forms!frm_Print!lst_Items is not part of my code, so I have no context for debugging.

      I’d urge you to post your full code block in a forum such as UtterAccess.com to get assistance.

      1. Scott Hall

        I actually experienced the same thing as Jaffer.

        Importing the function as is caused my database to stop recognizing Forms as a collection, causing previously working code to fail. I updated the Enum to the Access_XXXX schema and the problem went away.

        This was on Access 2016

  12. Jan Coupé

    How do I change the above Query SQL if I want a list of the Acces Objects but within the Object Querys a list that makes a difference between the different query’s (selection query, add query, table-makedelete, …) that is displayed be Acces. By the flags? How to incorporate in the Query SQL?

    1. Daniel Pineault Post author

      Here’s a quick version to illustrate the approach, but the Switch statement is incomplete as you’d need to add all the possible flag values and their meanings to complete the query properly:

      SELECT MsysObjects.Name AS [List OF TABLES], MsysObjects.Type, Switch(MsysObjects.Type=1,"Local Table",MsysObjects.Type=4,"Linked ODBC Table",MsysObjects.Type=5,"Query",MsysObjects.Type=6,"Linked Table",MsysObjects.Type=-32768,"Form",MsysObjects.Type=-32764,"Report",MsysObjects.Type=-32766,"Macro",MsysObjects.Type=-32761,"VBA Module") AS [Object Type]
FROM MsysObjects
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*"))
ORDER BY Switch(MsysObjects.Type=1,"Local Table",MsysObjects.Type=4,"Linked ODBC Table",MsysObjects.Type=5,"Query",MsysObjects.Type=6,"Linked Table",MsysObjects.Type=-32768,"Form",MsysObjects.Type=-32764,"Report",MsysObjects.Type=-32766,"Macro",MsysObjects.Type=-32761,"VBA Module"), MsysObjects.Type, MsysObjects.Name;

      Another, probably better approach, would be to build a simple cross-reference table containing the Object Types, Flags and plain English meanings so you could simply use the table as a normal join in the FROM clause and use the fields directly in a query. No more long Switch Statements necessary. I don’t have the time right now, but will try to put together a small demo shortly.

      1. Tiffany

        I wasn’t able to access the video you linked above. Could you help me? Thanks!

        What I am really looking for is to be able to query a list of all field names in db tables and queries. I know Object names can be found in the MSysObjects table, but I don’t know where the field info is stored. I have seen many methods of getting the fields of a specific table, but this is time consuming and as field names can change over the course of development, I don’t want to have to repeat that process all over again frequently to keep things accurate. Using the database documenter isn’t very helpful for my purposes either. Can you point me in the right direction? Thanks again!!

        1. Daniel Pineault Post author

          It a question of walking through the collection of tables and walking through the fields for each table/query.

          For tables, you could do something like:

          Public Sub tbl_ListTablesAndFields(Optional bShowSys As Boolean = False)
    Dim db                    As DAO.Database
    Dim tdfs                  As DAO.TableDefs
    Dim tdf                   As DAO.TableDef
    Dim fld                   As DAO.Field

    On Error GoTo Error_Handler

    Set db = CurrentDb()
    Set tdfs = db.TableDefs
    For Each tdf In tdfs    'loop through all the fields of the tables
        If (left(tdf.Name, 4) = "MSys" Or left(tdf.Name, 1) = "~") _
           And bShowSys = False Then GoTo Continue
        Debug.Print tdf.Name
        If tdf.Fields.Count > 0 Then 'loop through all the fields of the table
            For Each fld In tdf.Fields
                Debug.Print , fld.Name
            Next
        End If

Continue:
    Next

Error_Handler_Exit:
    On Error Resume Next
    Set fld = Nothing
    Set tdf = Nothing
    Set tdfs = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: tbl_ListTablesAndFields" & 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 Sub

          and for queries you can do very similarly:

          Public Sub qry_ListQueriesAndFields()
    Dim db                    As DAO.Database
    Dim qdfs                  As DAO.QueryDefs
    Dim qdf                   As DAO.QueryDef
    Dim fld                   As DAO.Field

    On Error GoTo Error_Handler

    Set db = CurrentDb()
    Set qdfs = db.QueryDefs
    For Each qdf In qdfs    'loop through all the fields of the queries
        Debug.Print qdf.Name
        If qdf.Fields.Count > 0 Then 'loop through all the fields of the query
            For Each fld In qdf.Fields
                Debug.Print , fld.Name
            Next
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    Set fld = Nothing
    Set qdf = Nothing
    Set qdfs = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: qry_ListQueriesAndFields" & 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 Sub

          So you simply run these functions whenever you wish to extract a listing.

  13. amorosik

    Really very useful article
    And how to edit the Connect field to change the information of tables linked to odbc data source?
    Basically the same Access procedure that can first use the tables on a db server1, then the same tables on db server2

  14. Doug Forester

    I am interested in “exporting” an Access database’s forms to a system such as Qt (C++). I would like to understand how a form in Access is stored so that I can copy its elements. Do you have an idea of how I would proceed?
    Thanks.

    1. Daniel Pineault Post author

      There is little to do, and you have to restart development.

      You can use the undocumented Application.SaveAsText functions to see the output, but there is little you can do with it.

  15. P.J. Thys

    Very Helpfull.
    I wonder, is there a way to count all open database objects, ie tables, queries, forms and reports.
    forms.count works great for open forms but I can’t find the equivalent commands for tables, queries and reports.

    1. Daniel Pineault Post author

      For Forms, you can use the Forms object. For Reports, you can use the Reports object.

      https://learn.microsoft.com/en-us/office/vba/api/access.forms
      https://learn.microsoft.com/en-us/office/vba/api/access.reports

      For other objects you can use the sysCmd(acSyscmdGetObjectState, …, …), so sysCmd(acSyscmdGetObjectState, acTable, “YourTableName”) or sysCmd(acSyscmdGetObjectState, acQuery, “YourQueryName”)

      You can also use the IsLoaded property, but be careful here as being opened in design view counts as loaded. That said, if you have a controlled system where users can’t do such things, it too can be used to list which object are open, or not. There’s an example for queries found at: https://learn.microsoft.com/en-us/office/vba/api/access.allqueries, but you can extend the same principle to other objects. Here’s a similar example for tables: https://learn.microsoft.com/en-us/office/vba/api/access.alltables

      It really depends on what you are trying to do exactly.

      1. Peter Thys

        Thank you for your swift response. For personal reasons I was not able to look into it earlier.
        For a friend I’m trying to build a database. To make it more or less foolproof I’m building some kind of menusystem with buttons he has to click in a certain order (from 1 to 10).
        Some buttons open a table or a query where he can check and / or correct data.
        Other buttons open a actionquery (add, update or delete data). If there are underlying tables or queries open, and my friend forgets to close them before running the actionquery, errors can arrise. To prevent this I would like to write a procedure which closes all open tables, queries and forms (and reports in the future) before the actionquery runs. Ideally the menuform is not closed, but it is not a big problem to open it again as last step in the CloseAll Procedure and point the ‘cursor’ at the button he last pressed.

        1. Daniel Pineault Post author

          I haven’t posted all of these in the past (I will now), but here are a few function you can use if you wish:

          '---------------------------------------------------------------------------------------
' 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:
' ~~~~~~
' Call CloseAllOpenTables
'
' 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

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenQueries
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Queries 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:
' ~~~~~~
' Call CloseAllOpenQueries
'
' 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 CloseAllOpenQueries() As Boolean
    On Error GoTo Error_Handler
    Dim oQrys                 As Object
    Dim oQry                  As Access.AccessObject

    Set oQrys = CurrentData.AllQueries

    For Each oQry In oQrys    'Loop all the queries
        If oQry.IsLoaded = True Then 'check if it is open
            DoCmd.Close acQuery, oQry.Name, acSaveNo
        End If
    Next oQry
    
    CloseAllOpenQueries = True

Error_Handler_Exit:
    On Error Resume Next
    Set oQry = Nothing
    Set oQrys = Nothing
    Exit Function

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

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenForms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Forms 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:
' ~~~~~~
' Call CloseAllOpenForms
'
' 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 CloseAllOpenForms() As Boolean
    On Error GoTo Error_Handler
    Dim oFrms                 As Object
    Dim oFrm                  As Access.Form

    Set oFrms = Application.Forms    'Collection of all the open forms

    For Each oFrm In oFrms    'Loop all the forms
        DoCmd.Close acForm, oFrm.Name, acSaveNo
    Next oFrm
    
    CloseAllOpenForms = True

Error_Handler_Exit:
    On Error Resume Next
    Set oFrm = Nothing
    Set oFrms = Nothing
    Exit Function

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

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenReports
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Reports 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:
' ~~~~~~
' Call CloseAllOpenReports
'
' 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 CloseAllOpenReports() As Boolean
    On Error GoTo Error_Handler
    Dim oRpts                 As Object
    Dim oRpt                  As Access.Report

    Set oRpts = Application.Reports    'Collection of all the open reports

    For Each oRpt In oRpts    'Loop all the reports
        DoCmd.Close acReport, oRpt.Name, acSaveNo
    Next oRpt
    
    CloseAllOpenReports = True

Error_Handler_Exit:
    On Error Resume Next
    Set oRpt = Nothing
    Set oRpts = Nothing
    Exit Function

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

'---------------------------------------------------------------------------------------
' 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:
' ~~~~~~
' Call CloseAllOpenMacros
'
' 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
        2. Peter Thys

          Based on your comments and with the help of links you provided I made the followin function:
          Public Function AllTables()
          ‘https://learn.microsoft.com/en-us/office/vba/api/access.alltables
          Dim obj As AccessObject, dbs As Object
          Set dbs = Application.CurrentData
          ‘ Search for open AccessObject objects in AllTables collection.
          For Each obj In dbs.AllTables
          If obj.IsLoaded = True Then
          Do Until obj.IsLoaded = False
          DoCmd.Close
          Loop
          End If
          Next obj
          End Function

          Without the do until loop the function had to be run a few times, because not all tables were closed, just 2 or 3 tables.
          Made the same function for queries and forms, where I can leave the menuform open 🙂 Thank for pointing me in the right direction

          1. Peter Thys

            Wow, I can see you are a real pro 🙂
            My previous reply crossed your answer (Lesson learned: always refresh webpage before replying)
            I will use your functions, much better with nice errorhandling.
            Thank you very much.

  16. Peter Thys

    Keeping the menuform open was a piece of cake:

    For Each oFrm In oFrms ‘Loop all the forms

    ‘if oFrm.Name is name of the menuform change the oFrm.Name to close to “” so nothing to close
    If oFrm.Name = “HOOFDMENU” Then oFrm.Name = “”

    DoCmd.Close acForm, oFrm.Name, acSaveNo
    Next oFrm

    1. Daniel Pineault Post author

      Instead do:

      For Each oFrm In oFrms    'Loop all the forms
    'Skip Menu form
    If oFrm.Name <> "HOOFDMENU" Then DoCmd.Close acForm, oFrm.Name, acSaveNo
Next oFrm
  18. Lorenzo

    hi Daniel, the function described above is really useful. Thank you.
    Using it in a DB, I discovered that I have, among the local tables, a table with a very strange name:
    “f_9E8203D96A754B0890DAF9414007C362_Data”.
    I don’t know why this table exists,
    I can’t see it of objects.
    I can’t delete it by code.

    Do you have any suggestions?

    Thanks in advance

    Lorenzo

    1. Daniel Pineault Post author

      These are system tables. I believe related to the MVF and/or attachments. They only exist in ‘newer’ accdb databases. Basically, they are created by Access itself, so don’t mess with them or you may do irreparable damage to your database!