Quote:
Originally Posted by Kganesan
Is there a way to take a collections object of modules and read each
function and procedure and any declarations in it? I have 25 databases in Access that I need to programmatically go thru each module. Does anyone have
a method for doing this?
The following query will return a list of all modules:
-
SELECT MsysObjects.Type, MsysObjects.Name FROM MsysObjects
-
WHERE ([Name] Not Like '~*') AND ([Name] Not Like 'MSys*')
-
AND [Type] = -32761
-
ORDER BY Type, Name;
-
Save this query as qryModuleList.
Create new table to store the function list called tblFunctionList with the following fields:
FunctionID (PrimaryKey - Autonumber)
ModuleName
FunctionName
-
Function findFunctions()
-
Dim db As DAO.Database
-
Dim rs1 As DAO.Recordset
-
Dim rs2 As DAO.Recordset
-
Dim mdl As Module
-
Dim i As Long
-
Dim k As Long
-
Dim strLine As String
-
Dim intF As Integer
-
Dim strFunc(5) As String
-
-
strFunc(0) = "Private Sub"
-
strFunc(1) = "Public Sub"
-
strFunc(2) = "Sub"
-
strFunc(3) = "Private Function"
-
strFunc(4) = "Public Function"
-
strFunc(5) = "Function"
-
-
Set db = CurrentDb
-
Set rs1 = db.OpenRecordset("qryModuleList")
-
Set rs2 = db.OpenRecordset("tblFunctionList")
-
-
rs1.MoveFirst
-
Do until rs1.EOF
-
-
' Search the module for the function declaration line
-
i = 1
-
DoCmd.OpenModule (rs1![Name])
-
Set mdl = Modules(rs1![Name])
-
While i <= mdl.CountOfLines
-
strLine = mdl.Lines(i, 1)
-
-
j = 0
-
Do While j <= UBound(strFunc)
-
intProc = InStr(MyLine, strFunc(j))
-
If (intF = 1) Then
-
With rs2
-
.AddNew
-
!ModuleName = rs1![Name]
-
!FunctionName = strLine
-
.Update
-
End With
-
Exit Do
-
End If
-
j = j + 1
-
Loop
-
-
i = i + 1
-
Wend
-
DoCmd.Close acModule, rs1![Name]
-
Set mdl = Nothing
-
rs1.MoveNext
-
Loop
-
-
rs1.Close
-
rs2.Close
-
Set rs1=Nothing
-
Set rs2=Nothing
-
Set db=Nothing
-
-
End Function
-
