QUESTION: Get All Procedure Names from Modules

[boblarson]

Anyone have an idea on how I can get all procedure names from a module in an external database? I'm trying to build a tool which will let me compare two databases to see where they have common items in them (or items named the same actually).

I've gotten the code to get the table, query, form, report, macro, and module names but I still am wanting to go to each procedure and get all procedure names as well. So, I'll keep plugging away at it but if anyone knows I would be grateful for the information.

 

[Banana]

You would have to use Module object and run over each line as I don't think there is a Procedure collection...

Dim m As Module
Dim l As Long

Set m = CurrentProject.AllModules(0)

For l = 1 to m.CountOfLines
   Debug.Print m.ProcOfLine(l)
Next

This will give you all procedures albeit repetitive as many lines as there are in each procedures. You could set up a recordset to take procedure name as a key and do a resume next on key violation error to get a distinct list.

Hope that gets you started.

 

[Banana]

Or, to cut down on mindless looping...

Do Until l >= m.CountOfLines
   Debug.Print m.ProcOfLine(l)
   l + m.CountProcLines(m.ProcOfLine(l))
Loop

 

[boblarson]

unfortunately, it isn't much help. I've gotten as far as getting the module names from the external database. I just don't know how to open them and get the procedures out of them.

And, I can't use CurrentProject as it isn't the current project. To get the external module information I used:

        For Each varItem In db.Containers("Modules").Documents

But that's as far as I currently can get. I assume I'm going to have to iterate down line by line to find anything with a Function or Sub in the line (or something like that). I just haven't figured out yet how to iterate through, line by line

 

[boblarson]

I actually would go download the source code for V-Tools to see how he does it, but his site is blocked by my work.

 

[Banana]

Hmm. I see.


Given that CurrentProject is a child of Application object, I wonder if we retrieved an object via external database that would refers its Application object and then its CurrentProject....

Dim a As Access.Application
Dim p As CurrentProject

Set a = New Access.Application
Set p = a.CurrentProject
a.OpenCurrentDatabase ("C:\Users\banana\Documents\Nwind.mdb")

Debug.Print p.AllModules(0).Name

a.Quit

Set p = Nothing
Set a = Nothing

I've never done that before and there may be a better way of doing it, but it does seem to work.

 

[boblarson]

Well, not any better than what I currently have. I have the module names. That isn't a problem. It is getting the procedure names OUT of the modules that is the problem. And that part is where I'm stuck.

 

[boblarson]

Hey Banana:

If you can figure out a way to convert this from .NET to VBA - Let me know as it looks like it might just do what I want (but I have to use Access VBA):

http://support.microsoft.com/kb/266387

 

[Banana]

I'm sorry; my sample code could have been more clearer; the last one was specifically to solve the question about getting a currentproject so you can reference AllModules collection which you need to be able to get a module object in order to get procedure names. (whew, that's a mouthful!). Basically combine the previous one with the one before that to get you the procedure names.

PS I looked over the article quickly and it basically does the same thing as I posted above- automate Access be creating a new instance of access then use that reference to manipulate its Currentproject -> AllModules -> Module -> Procedures.

 

[boblarson]

I just found this, which I'm going to try. It was in the Access 2003 VBA Documentation on the MSDN website:

Public Function AllProcs(ByVal strModuleName As String)

    Dim mdl As Module
    Dim lngCount As Long
    Dim lngCountDecl As Long
    Dim lngI As Long
    Dim strProcName As String
    Dim astrProcNames() As String
    Dim intI As Integer
    Dim strMsg As String
    Dim lngR As Long

    ' Open specified Module object.
    DoCmd.OpenModule strModuleName
    
    ' Return reference to Module object.
    Set mdl = Modules(strModuleName)
    
    ' Count lines in module.
    lngCount = mdl.CountOfLines
    
    ' Count lines in Declaration section in module.
    lngCountDecl = mdl.CountOfDeclarationLines
    
    ' Determine name of first procedure.
    strProcName = mdl.ProcOfLine(lngCountDecl + 1, lngR)
    
    ' Initialize counter variable.
    intI = 0
    
    ' Redimension array.
    ReDim Preserve astrProcNames(intI)
    
    ' Store name of first procedure in array.
    astrProcNames(intI) = strProcName
    
    ' Determine procedure name for each line after declarations.
    For lngI = lngCountDecl + 1 To lngCount
        ' Compare procedure name with ProcOfLine property value.
        If strProcName <> mdl.ProcOfLine(lngI, lngR) Then
            ' Increment counter.
            intI = intI + 1
            strProcName = mdl.ProcOfLine(lngI, lngR)
            ReDim Preserve astrProcNames(intI)
            ' Assign unique procedure names to array.
            astrProcNames(intI) = strProcName
        End If
    Next lngI
    
    strMsg = "Procedures in module '" & strModuleName & "': " & vbCrLf & vbCrLf
    For intI = 0 To UBound(astrProcNames)
        strMsg = strMsg & astrProcNames(intI) & vbCrLf
    Next intI
    
    ' Message box listing all procedures in module.
    MsgBox strMsg
End Function

 

[boblarson]

Of course I will have to modify slightly as the code, as written, is more for the current database.

 

[Banana]

Very cool.

I actually didn't know about Modules collection, having thought they could only be accessed via AllModules collection. (Am I only one to wonder the point of having a Forms collection for open forms and AllForms collection for all forms? Seems to me they should be just one collection with a property tacked on to indicate if they're loaded or not...)

 

[boblarson]

The modification that worked is provided courtesy of MVP Doug Steele (in a post on Utter Access):

Public Function AllProcs(ByVal strDatabasePath As String, ByVal strModuleName As String)
    Dim appAccess As Access.Application
    Dim db As Database
    Dim mdl As Module
    Dim lngCount As Long
    Dim lngCountDecl As Long
    Dim lngI As Long
    Dim strProcName As String
    Dim astrProcNames() As String
    Dim intI As Integer
    Dim strMsg As String
    Dim lngR As Long

    Set appAccess = New Access.Application

    appAccess.OpenCurrentDatabase strDatabasePath
    ' Open specified Module object.
    appAccess.DoCmd.OpenModule strModuleName
    ' Return reference to Module object.
    Set mdl = appAccess.Modules(strModuleName)
    ' Count lines in module.
    lngCount = mdl.CountOfLines
    ' Count lines in Declaration section in module.
    lngCountDecl = mdl.CountOfDeclarationLines
    ' Determine name of first procedure.
    strProcName = mdl.ProcOfLine(lngCountDecl + 1, lngR)
    ' Initialize counter variable.
    intI = 0        ' Redimension array.
    ReDim Preserve astrProcNames(intI)
    ' Store name of first procedure in array.
    astrProcNames(intI) = strProcName
    ' Determine procedure name for each line after declarations.
    For lngI = lngCountDecl + 1 To lngCount
        ' Compare procedure name with ProcOfLine property value.
        If strProcName <> mdl.ProcOfLine(lngI, lngR) Then
            ' Increment counter.
            intI = intI + 1
            strProcName = mdl.ProcOfLine(lngI, lngR)
            ReDim Preserve astrProcNames(intI)
            ' Assign unique procedure names to array.
            astrProcNames(intI) = strProcName
        End If
    Next lngI
    strMsg = "Procedures in module '" & strModuleName & "': " & vbCrLf & vbCrLf
    For intI = 0 To UBound(astrProcNames)
        strMsg = strMsg & astrProcNames(intI) & vbCrLf
    Next intI
    ' Message box listing all procedures in module.
    Debug.Print strMsg
    appAccess.CloseCurrentDatabase
    appAccess.Quit
    Set appAccess = Nothing
End Function

Now just modifying from the message box to putting it in to my table.