Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: Way to print all the forms, reports, query VB code?

    We are trying to document an Access system that has NEVER been mapped. Many tables, Forms, Views, Reports, Queries.

    Many of the VB code scripts has complex SQL select clauses using foreign keys that are not described in the database as foreign keys!

    We would like to find out what the foreign keys are and designate them in the tables so the schema shows up correctly.

    Any way to get all the VB code printed to a file?

    Thanks,

    Deaf

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Here's a procedure that will print all the VBA code of all Modules, Form Modules, Report Modules and the SQL expression of all Queries in an Access database.
    Code:
    Public Sub PrintVBCode(Optional ByVal ObjectType As Long, Optional ByVal Path As String)
    
        Dim obj As AccessObject
        Dim mdl As Module
        Dim qdf As DAO.QueryDef
        Dim strFile As String
        Dim i As Long
        
        If Len(Path) = 0 Then Path = CurrentProject.Path
        If Right(Path, 1) <> "\" Then Path = Path & "\"
        
        ' Forms
        '
        If ObjectType = 0 Or ObjectType = acForm Then
            For Each obj In Application.CurrentProject.AllForms
                DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
                Set mdl = Forms(obj.Name).Module
                strFile = Path & "Form_" & obj.Name & ".mdl"
                Open strFile For Output As #1
                Print #1, "' ==== " & obj.Name & " ===="
                Print #1, "'"
                For i = 1 To mdl.CountOfLines
                    Print #1, mdl.Lines(i, 1)
                Next i
                Close #1
                DoCmd.Close acForm, obj.Name
            Next obj
        End If
        
        ' Reports
        '
        If ObjectType = 0 Or ObjectType = acReport Then
            For Each obj In Application.CurrentProject.AllReports
                DoCmd.OpenReport obj.Name, acDesign, , , , acHidden
                Set mdl = Reports(obj.Name).Module
                strFile = Path & "Report_" & obj.Name & ".mdl"
                Open strFile For Output As #1
                Print #1, "' ==== " & obj.Name & " ===="
                Print #1, "'"
                For i = 1 To mdl.CountOfLines
                    Print #1, mdl.Lines(i, 1)
                Next i
                Close #1
                DoCmd.Close acReport, obj.Name
            Next obj
        End If
        
        ' Modules
        '
        If ObjectType = 0 Or ObjectType = acModule Then
            For Each obj In Application.CurrentProject.AllModules
                DoCmd.OpenModule obj.Name
                Set mdl = Modules(obj.Name)
                strFile = Path & "Module_" & obj.Name & ".mdl"
                Open strFile For Output As #1
                Print #1, "' ==== " & obj.Name & " ===="
                Print #1, "'"
                For i = 1 To mdl.CountOfLines
                    Print #1, mdl.Lines(i, 1)
                Next i
                Close #1
            Next obj
        End If
        
        ' Queries
        '
        If ObjectType = 0 Or ObjectType = acQuery Then
            For Each qdf In CurrentDb.QueryDefs
                If Left(qdf.Name, 1) <> "~" Then
                    strFile = Path & "Query_" & qdf.Name & ".sql"
                    Open strFile For Output As #1
                    Print #1, "- ==== " & qdf.Name & " ===="
                    Print #1, "-"
                        Print #1, qdf.SQL
                    Close #1
                End If
            Next qdf
        End If
        
    End Sub
    If you call it without any parameter, the code (VBA and SQL) of all objects will be printed into files in the database folder. the name of each file consists in :
    ObjectType_ObjectName.Extension
    e.g. Module_Cls_Std_DataConnector.mdl (for a Class Module), Form_Frm_OrderManager.mdl (form a Fom Module), Report_Rpt_Custom.mdl (for a Report Module), Query_Qry_Customers.sql (for a query).

    The procedure accepts 2 optional parameters.

    The first parameter ObjectType allows you to specify the type of object for which you want to extract the code.
    e.g. PrintVBCode ObjectType:= acForm will extract the code of all forms only.
    You can use the intrinsic Access constants acQuery (= 1), acForm (= 2), acReport (= 3), acModule (= 5) or their numeric counterparts (i.e. PrintVBCode ObjectType:= acForm is the same as PrintVBCode ObjectType:= 2)

    The second parameter Path allows you to specify a different folder for the output files.
    e.g. PrintVBCode Path:= "U:\Access\CF_Sit\CF_Sit_3\Documents"
    Note: The folder must exist!

    You can specify both parameters when calling the procedure:
    PrintVBCode ObjectType:= AcModule, Path:= "U:\Access\CF_Sit\CF_Sit_3\Documents"
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The file IS the access db. All separated by forms, tables, queries, etc.

  4. #4
    Join Date
    Jul 2012
    Posts
    10
    We will give it a whirl! Restoring a DB right now with pgAdmim but will check it out.

    Thanks very much.

    Deaf

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!

    You can also download and install the mz-tools freeware which, among many useful functionalities, comprises an object documentor that yields HTML reports: http://www.mztools.com/v3/mztools3.aspx
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •