505.369.1650 [email protected]

This VBA procedure shows you how to create a simple list of the objects in a Microsoft Access database.  The list it creates includes the object type, object name, and object description, which can be parsed using the Excel file in the download.

Descriptions can be added to the objects by right clicking over each object, selecting the option, and filling the Description box.

To use the procedure, you may type the procedure name in the VBA Immediate window, like AllDescriptions, and then press Enter.

Code (some lines may wrap):

Public Sub AllDescriptions()
' This procedure finds the names and descriptions (from the property
' sheet) for all the types of objects in the database.  It creates results
' in the Immediate window, like "Form+frmOrdersEdit:Add/edit Orders data",
' where the object type = Form, object name = frmOrdersEdit, and the object
' description = Add/edit Orders data.

' You can copy the results to column A in an Excel  that has the
' following formulas to parse the list:
' Object Type =LEFT(A1,SEARCH("+",A1)-1)
' Object Name =RIGHT(LEFT(A1,SEARCH(":",A1)-1),LEN(LEFT(A1,SEARCH(":",A1)-1))-SEARCH("+",A1))
' Object Desc =RIGHT(A1,LEN(A1)-SEARCH(":",A1))

' AllDescriptions() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected]
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_AllDescriptions

    Dim qdf As QueryDef
    Dim tdf As TableDef
    Dim obj As 
    Dim varProperty
    Dim strType As String
   
    ' Queries.
    For Each qdf In .
        If Left(qdf.Name, 1)  "~" Then
            varProperty = ""
            varProperty = qdf.Properties("Description")
             "Query" & "+" & qdf.Name & ":" & varProperty
        End If
    Next qdf
   
    ' Tables.
    For Each tdf In CurrentDb.
        If Left(tdf.Name, 4)  "MSys" Then
            varProperty = ""
            varProperty = tdf.Properties("Description")
            ' Set the type based on whether or not the definition
            ' has a connect string, signifying that it is linked.
            If Len(tdf.Connect) > 0 Then
                strType = "Table Link"
            Else
                strType = "Table"
            End If
            Debug.Print strType & "+" & tdf.Name & ":" & varProperty
        End If
    Next tdf
   
    ' Pages.
    For Each obj In CurrentProject.AllDataAccessPages
        varProperty = ""
        varProperty = CurrentDb.("DataAccessPages").(obj.Name).Properties("Description")
        Debug.Print "Page" & "+" & obj.Name & ":" & varProperty
    Next obj
   
    ' Forms.
    For Each obj In CurrentProject.AllForms
        varProperty = ""
        varProperty = CurrentDb.Containers("Forms").Documents(obj.Name).Properties("Description")
        Debug.Print "Form" & "+" & obj.Name & ":" & varProperty
    Next obj
   
    ' Macros.
    For Each obj In CurrentProject.AllMacros
        varProperty = ""
        varProperty = CurrentDb.Containers("").Documents(obj.Name).Properties("Description")
        Debug.Print "Macro" & "+" & obj.Name & ":" & varProperty
    Next obj
   
    ' Modules.
    For Each obj In CurrentProject.AllModules
        varProperty = ""
        varProperty = CurrentDb.Containers("Modules").Documents(obj.Name).Properties("Description")
        Debug.Print "Module" & "+" & obj.Name & ":" & varProperty
    Next obj
   
    ' Reports.
    For Each obj In CurrentProject.AllReports
        varProperty = ""
        varProperty = CurrentDb.Containers("Reports").Documents(obj.Name).Properties("Description")
        Debug.Print "Report" & "+" & obj.Name & ":" & varProperty
    Next obj
   
Exit_AllDescriptions:
    On Error Resume Next
    Exit Sub
   
Err_AllDescriptions:
    Select Case Err.Number
    Case 3270
        ' There was no description property for this object.
        Resume Next
    Case Else
        MsgBox Err.Number & " " & Err.Description, vbCritical, "AllDescriptions"
        Resume Exit_AllDescriptions
    End Select

End Sub

Download Code (with Excel code):

AllDescriptions.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.