Code in Functions and Procedures

[Buja]

Is the code in functions and procedures available by stepping through collections?

For example, the SQL code of a query is available via
...QueryDefs(queryname).SQL

I am using DAO in Access 2002.

Thanks in advance.

 

[Buja]

Thanks for responding.

I am trying to document an existing Access system (tables, queries, forms, reports, modules ...). I was able to get all the query names and their sql statements via ...QueryDefs.Name and QueryDefs.sql.
Now I am trying to get all the functions and procedures names and their code programically. I thought this would be possible since Access is able to via the "documentor".

I was looking at "Containers.Documents"... but that didn't have the code. I will try "Access.Module"... and see if that works.

Hope this is understandable!

Thanks.

 

[ChrisO]

Would something like this do?

Option Explicit
Option Compare Text


Sub Test()

    OutPutModule "mdlProgramVersion", "C:\"

End Sub


Sub OutPutModule(ByVal strModuleName As String, _
                 ByVal strPath As String)

    DoCmd.OutputTo acOutputModule, strModuleName, acFormatTXT, strPath & strModuleName & ".txt"
    
End Sub

Limited testing but it seems to work OK.

Regards,
Chris.

 

[ChrisO]

A bit more code but maybe a better way: -

[color=green]'
'                             Test only...
'      None of this code is currently used within the program at runtime.     
'             It does not need a reference to DAO or ADO.                     
'                                                                             
'[/color]
Option Explicit
Option Compare Text

Public Declare Function apiCreatePath Lib "Imagehlp.dll" _
                 Alias "MakeSureDirectoryPathExists" (ByVal strPath As String) As Long


[color=green]'   Please retain, this may be useful for future documentation.
'   For best results switch Notepad to Courier New font.[/color]
Public Sub OutputAllGlobalModules()
    Dim strDumpPath            As String
    
    [color=green]'   Still required for old DAO reference.
    '   No other DAO or ADO reference is required.[/color]
    Const conDbOpenDynaset     As Long = 2
    
    [color=green]'   System table entry for Global Modules.[/color]
    Const conSystemTableModule As Long = -32761
    
    On Error GoTo ErrorHandler
     
    [color=green]'   Set the destination path and ensure that it exists.[/color]
    strDumpPath = "C:\AccessModuleTextDump\"
    apiCreatePath strDumpPath
    
    [color=green]'   Pull the conditional recordset, DAO or ADO notwithstanding.[/color]
    With CurrentDb.OpenRecordset(" SELECT Name" & _
                                 " FROM MSysObjects" & _
                                 " WHERE Type = " & conSystemTableModule & _
                                 " ORDER BY Name;", _
                                   conDbOpenDynaset)
                                   
        [color=green]'   Output all Global modules to the destination path.[/color]
        Do Until .EOF
            OutPutModuleToTextFile !Name, strDumpPath
            .MoveNext
        Loop
        
        [color=green]'   We opened it, so we will close it.[/color]
        .Close
    End With

ExitProcedure:
    Exit Sub

ErrorHandler:
    DisplayError "OutputAllGlobalModules", "mdlUtilityOnlyNotUsedInProgram"
    Resume ExitProcedure

End Sub


Public Sub OutPutModuleToTextFile(ByVal strModuleName As String, _
                                  ByVal strPath As String)

    On Error GoTo ErrorHandler

    [color=green]'   Export this modules text to the specified path.[/color]
    DoCmd.OutputTo acOutputModule, strModuleName, acFormatTXT, strPath & strModuleName & ".txt"
    
ExitProcedure:
    Exit Sub

ErrorHandler:
    DisplayError "OutPutModuleToTextFile", "mdlUtilityOnlyNotUsedInProgram"
    Resume ExitProcedure
    
End Sub


[color=green]'   Global error handler for all Procedures. (Except the ones that don't call it.)[/color]
Public Sub DisplayError(ByVal strProcedureName As String, _
                        ByVal strModuleName As String, _
               Optional ByVal strAdditionalInfo As String = "")

    Dim strMessage As String

    [color=green]'   First get a copy of the current error number and description.[/color]
    strMessage = "Error in Module: " & strModuleName & vbNewLine & _
                 "Procedure: " & strProcedureName & vbNewLine & vbNewLine & _
                 "Error Number: " & Err.Number & vbNewLine & _
                 "Error Description: " & Err.Description

    [color=green]'   Now that the error info has been saved we can use error handling.[/color]
    On Error GoTo ErrorHandler

    [color=green]'   Add any additional info that may have been passed.[/color]
    If strAdditionalInfo <> "" Then
        strMessage = strMessage & vbNewLine & vbNewLine & _
                     "Additional information:" & vbNewLine & _
                     strAdditionalInfo
    End If
            
    [color=green]'   Display the original error.[/color]
    MsgBox strMessage, vbCritical, "Runtime error detected"
    
ExitProcedure:
    Exit Sub

ErrorHandler:
    [color=green]'   Do not recall this error procedure...simply display the new error.[/color]
    MsgBox "Error in Module: mdlHandleErrors" & vbNewLine & _
           "Procedure: DisplayError" & vbNewLine & vbNewLine & _
           "Error Number: " & Err.Number & vbNewLine & _
           "Error Description: " & Err.Description
                 
    Resume ExitProcedure
    
End Sub

Maybe?

Regards,
Chris.

 

[Buja]

Chris:

Wow, you did a lot of work. Thanks. This is probably beyond me but I'll take a look. I saw you earlier solution using "OutputTo". This is nice but I was trying to trap the procedure in a string via code so that I can update a table ...

Thanks for all your help.