Solved

Gathering a list of Forms, Reports, Macros and Modules

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Run-time 91 error, not sure why 3 26
Replace special characters in Access VBA 8 28
Access MDB/PDF 21 32
Error in SQL Query 36 36
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

760 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

22 Experts available now in Live!

Get 1:1 Help Now