?
Solved

Gathering a list of Forms, Reports, Macros and Modules

Posted on 2013-01-04
4
Medium Priority
?
479 Views
Last Modified: 2013-01-07
Using VBA, how can you gather a list of Forms, Reports, Macros and Modules in an Access database other than the current database?
0
Comment
Question by:dsoderstrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38744601
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38744825
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 1000 total points
ID: 38744827
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
 

Author Closing Comment

by:dsoderstrom
ID: 38750692
Thank you both for the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question