First Published 5 July 2023


Database object types can easily be identified using the Type field in the MSysObjects system table:


In addition, table and query subtypes can easily be identified using the Flags field in the same MSysObjects system table. For example, the 10 types of query have Flags values:


NOTE:
Temp queries are used in the record sources for forms and reports and for the row sources of combo box and listbox controls

Similarly, different Flags values identify the different types of linked and system tables

The image below is taken from my article Remove Deleted Objects from the MSysObjects table and shows information about the meaning of most of the Type & Flags values you may find in that system table


However it is not possible to distinguish between standard and class modules using this method. Both types have Flags = 0.

Instead, we can use the Type property of each module where the Type property value = 0 for standard modules and 1 for class modules


For more information, see the Microsoft help article: Module.Type property



CODE:

Copy all code to a standard module e.g. modModuleType

a)   Identify the type for a specified module in the current database and list it to the immediate window

Sub CheckModuleType(strModuleName As String)

On Error GoTo Err_Handler

Dim obj As Object

      ' Open module to include in Modules collection
      DoCmd.OpenModule strModuleName

      ' Return reference to Module object
      Set obj = Modules(strModuleName)

      ' Check Type property
      Select Case obj.Type
      Case 1
            strType = "Class"
      Case 0
      strType = "Standard"
      End Select

      Debug.Print "Module: " & obj.Name, " Type: " & strType
      DoCmd.Close acModule, strModuleName

Exit_Handler:
      Exit Sub

Err_Handler:
      'err 2516 if specified module doesn't exist
      MsgBox "Error " & Err.Number & " in CheckModuleType procedure: " & vbCrLf & Err.Description
      Resume Exit_Handler

End Sub

      Example Usage



b)   List the type for all modules in the current database to the immediate window
      Use in combination with the previous code (or combine into one procedure)

Sub CheckAllModuleTypes()

Dim obj As Object

For Each obj In CurrentProject.AllModules
     CheckModuleType obj.Name
Next

End Sub

      Example Output:



c)   List the type for all modules in a specified database to the immediate window

Option Compare Database
Option Explicit

Dim obj As Object, strType As String, strModuleName As String, strPwd As String, strFilePath As String
Dim appAcc As Access.Application

'=======================================

Sub CheckAllModuleTypesExtDB()

On Error GoTo Err_Handler

      Set appAcc = New Access.Application

      strFilePath = "full path to your database here"
      strPwd = "enter database password here - if any"

      ' Open external database, set as current db & minimimize
      If strPwd <> "" Then
            appAcc.OpenCurrentDatabase strFilePath, False, strPwd
      Else
            appAcc.OpenCurrentDatabase strFilePath
      End If

      appAcc.DoCmd.Minimize

      Debug.Print strFilePath & IIf(strPwd <> "", vbCrLf & "PWD = " & strPwd, "") & vbCrLf

      ' loop through each module in external database
      For Each obj In appAcc.CurrentProject.AllModules

            strModuleName = obj.Name
            appAcc.DoCmd.OpenModule obj.Name

           ' Return reference to Module object
            Set obj = appAcc.Modules(strModuleName)

            ' Check Type property
            Select Case obj.Type
            Case 1
                  strType = "Class"
            Case 0
                  strType = "Standard"
            End Select

            Debug.Print "Module: " & obj.Name, " Type: " & strType
            DoCmd.Close acModule, strModuleName
      Next

      ' close external database
      appAcc.CloseCurrentDatabase
      appAcc.Quit

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err.Number & " in CheckAllModuleTypesExtDB procedure: " & vbCrLf & Err.Description
      Resume Exit_Handler

      End Sub

      Example Output (for a database with a password):





Download:

Click to download an example database with the code shown above:

      GetModuleType        ACCDB file - approx 0.4 MB (zipped)



Please Vote!

The above code will work for any database. However, the process could be made far simpler (and need far less code) if the module type property value was stored in the Flags field of the MSysObjects system table (as is already the case for tables and queries)

I have suggested this as a new feature in the Microsoft Access Feedback portal along with a similar request for the default view of forms and reports.
NOTE: Code to obtain the default view of forms/reports will be covered in a future article


If you think this is a good idea, please click the link below and vote for the suggestion.
      Microsoft Access Feedback Portal Suggestion

The Access team say they are committed to taking action on suggestions that receive a lot of votes. With your assistance, this suggestion may happen



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 5 July 2023

Return to Code Samples Page

Return to Top