Solved

Gathering a list of Forms, Reports, Macros and Modules

Posted on 2013-01-04
4
470 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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