Gathering a list of Forms, Reports, Macros and Modules

Using VBA, how can you gather a list of Forms, Reports, Macros and Modules in an Access database other than the current database?
dsoderstromAsked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
Here you go.  This code will create a table called tblDatabaseObjects with the info you're looking for.  Just set strDbName to the path and database name:

Sub RetrieveObjects()
    Dim strDbName As String
    Dim DOC As Document
    Dim cont As Container
    Dim wsp As Workspace
    Dim db2 As DATABASE
    Dim qry As QueryDef
    
    strDbName = "C:\ronTemp.mdb"
    
    'Create table tblDatabaseObjects
    If dcount("*", "MSysObjects", "MSysObjects.Name='tblDatabaseObjects'") > 0 Then
        CurrentDb.TableDefs.DELETE ("tblDatabaseObjects")
    End If
    CurrentDb.Execute "CREATE TABLE tblDatabaseObjects (" _
        & "objName TEXT(255), " _
        & "objType TEXT(10))"
        
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)
    Set db2 = wsp.OpenDatabase(strDbName)
    
    'Iterate to get list of objects (Tables, Forms, Reports, Macros, and Modules)
    For Each cont In db2.Containers
        For Each DOC In cont.Documents
            DoCmd.RunSQL "Insert Into tblDatabaseObjects (objName, objType) Values ('" & DOC.NAME & "','" & cont.NAME & "')"
        Next DOC
    Next cont

    'Iterate to get list of queries
    For Each qry In db2.QueryDefs
        DoCmd.RunSQL "Insert Into tblDatabaseObjects (objName, objType) Values ('" & qry.NAME & "','Queries')"
    Next
    db2.CLOSE
    Set db2 = Nothing
    
End Sub

Open in new window

0
 
etech0Commented:
I found  this somewhere a while back and used it.  Perhaps you can use another database instead of CurrentDb.

Public Sub AllDescriptions() 'i don't use this, but i think it works.

' 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 workbook 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 support@extramiledata.com.
' 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 AccessObject
    Dim varProperty
    Dim strType As String
   
    ' Queries.
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then
            varProperty = ""
            varProperty = qdf.Properties("Description")
            Debug.Print "Query" & "+" & qdf.Name & ":" & varProperty
        End If
    Next qdf
   
    ' Tables.
    For Each tdf In CurrentDb.TableDefs
        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.Containers("DataAccessPages").Documents(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("Scripts").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

Open in new window

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use this codes


Sub getOtherDbObjects()
Dim accDB As DAO.Database, dbPath, rs As DAO.Recordset
Dim tdf As DAO.TableDef, qd As DAO.QueryDef
Dim obj As AccessObject, j
dbPath = <complete path to the other db>  ' change this

Set accDB = OpenDatabase(dbPath)
     For Each tdf In accDB.TableDefs
          If Not tdf.Name Like "msys*" Then
          Debug.Print tdf.Name
          End If
     Next
     For Each qd In accDB.QueryDefs
          If Not qd.Name Like "~*" Then
          Debug.Print qd.Name
          End If
     
     Next
     
   ' Forms.
     For j = 0 To accDB.Containers("Forms").Documents.Count - 1
          Debug.Print "Form " & accDB.Containers("Forms").Documents(j).Name
     Next
    ' Macros.
    For j = 0 To accDB.Containers("Scripts").Documents.Count - 1
    
          Debug.Print "Macros " & accDB.Containers("Scripts").Documents(j).Name
    Next
   
    ' Modules.
    For j = 0 To accDB.Containers("Modules").Documents.Count - 1
        Debug.Print "Modules " & accDB.Containers("Modules").Documents(j).Name
    Next
   
    ' Reports.
    For j = 0 To accDB.Containers("Reports").Documents.Count - 1
        Debug.Print "Report " & accDB.Containers("Reports").Documents(j).Name
    Next

accDB.Close
End Sub

Open in new window

0
 
dsoderstromAuthor Commented:
Thank you both for the help.
0
All Courses

From novice to tech pro — start learning today.