Solved

Gathering a list of Forms, Reports, Macros and Modules

Posted on 2013-01-04
4
469 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
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 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 250 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now