Code Contribution: Obtaining MetaData about Code in Other Access d

[BlockNinja]

Here's some code I came up with for obtaining information about Modules in
another Access db. This can be useful if you're doing things like inserting
code (for me, I'm coming up with a way to simplify entering comments into
code in an Access db).

Set up:

Tables:

tbl_ModuleNames:
Fields:
moduleName - Text - 255 - Allow Zero Length
procName - Text - 255 - Allow Zero Length

tbl_Procedures:
Fields:
moduleName - Text - 255 - Allow Zero Length
procName - Text - 255 - Allow Zero Length
startingLine - Long Integer
bodyLine - Long Integer
countOfLines - Long Integer

Queries:

PQRYAPPEND_MODULES:
INSERT INTO tbl_ModuleNames ( moduleName, procName )
VALUES ([Enter Module], [Enter Procedure]);

PQRYSELECT_PROC_NAMES:
SELECT tbl_ModuleNames.moduleName, tbl_ModuleNames.procName
FROM tbl_ModuleNames
GROUP BY tbl_ModuleNames.moduleName, tbl_ModuleNames.procName
HAVING ((Not (tbl_ModuleNames.procName) Is Null));

PQRYAPPEND_PROCEDURES:
INSERT INTO tbl_Procedures ( moduleName, procName )
SELECT PQRYSELECT_PROC_NAMES.moduleName, PQRYSELECT_PROC_NAMES.procName
FROM PQRYSELECT_PROC_NAMES;

Code (you can call this any module you want):
Option Compare Database

Public Function CheckDB()
Dim db As DAO.Database
Set db = CurrentDb
Dim accobj As Access.Application
Set accobj = New Access.Application
accobj.OpenCurrentDatabase InputBox("Enter the Database File Name",
"Load", ""), True
Debug.Print accobj.Modules.Count
Dim i As Long
i = 0
db.Execute "delete * from tbl_Procedures", dbFailOnError
db.Execute "delete * from tbl_ModuleNames", dbFailOnError
For i = 0 To accobj.Modules.Count - 1
Dim mymod As Access.Module
Set mymod = accobj.Modules(i)
Debug.Print mymod.Name & " - " & mymod.CountOfDeclarationLines & " -
" & mymod.countOfLines
Dim x As Long
x = 1
For x = 1 To mymod.countOfLines
Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("PQRYAPPEND_MODULES")
qd.Parameters("Enter Module").Value = mymod.Name
qd.Parameters("Enter Procedure").Value = mymod.ProcOfLine(x,
vbext_pk_Proc)
qd.Execute dbFailOnError
Next
Set mymod = Nothing
Next
db.QueryDefs("PQRYAPPEND_PROCEDURES").Execute dbFailOnError
DoEvents
db.TableDefs.Refresh
DoEvents
db.Execute "delete * from tbl_ModuleNames", dbFailOnError
Dim rs As DAO.Recordset
Set rs = db.TableDefs("tbl_Procedures").OpenRecordset
If rs.EOF = False Then
rs.MoveFirst
While rs.EOF = False
Dim procLine, bodyLine, countOfLines As Long
procLine =
accobj.Modules(rs.Fields(0).Value).ProcStartLine(rs.Fields(1).Value,
vbext_pk_Proc)
bodyLine =
accobj.Modules(rs.Fields(0).Value).ProcBodyLine(rs.Fields(1).Value,
vbext_pk_Proc)
countOfLines =
accobj.Modules(rs.Fields(0).Value).ProcCountLines(rs.Fields(1).Value,
vbext_pk_Proc)
rs.Edit
rs.Fields(2).Value = procLine
rs.Fields(3).Value = bodyLine
rs.Fields(4).Value = countOfLines
rs.Update
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
accobj.Quit
db.Close
Set db = Nothing
End Function

The end result is a table with all of the distinct procedure names of each
module in an external database, with their starting, body, and counts of
lines. Hope this helps anyone!

 

[BlockNinja]

A few other quick points of mention:

References:
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Visual Basic for Applications Extensibility 5.3

The results of the above code are stored in tbl_Procedures, the "PQRY*" on
the queries means Production Query, should have probably been TQRY. (I've
been working too long....)

As far as the starting line and body line, the starting line is where the
section of code technically begins at (i.e. the first sub/function
technically starts right after the declaration section), but the actual
"public function" line starts at bodyLine. The countOfLines starts at the
startLine, not at the bodyLine, so take that into effect if you are inserting
text at the end of a function.

If anyone knows of a faster way to get the procedure names rather than
iterating through each line of code in a module, please let me know.

 

[Marshall Barton]

You can skip to the first procedure using the module's
CountOfDeclarationLines property. Then you can skip to the
next procedure using the ProcCountLines property. Here's a
simple outline of this kind of loop:

With Modules(strModName)
lngLinePos = .CountOfDeclarationLines
Do While lngLinePos < .CountOfLines
strProcName = .ProcOfLine(lngLinePos+1,lngProcKind)
lngProcLines = .ProcCountLines(strProcName,lngProcKind)
lngLinePos = lngLinePos + lngProcLines
Loop
End With
--
Marsh
MVP [MS Access]

Here's some code I came up with for obtaining information about Modules in
another Access db. This can be useful if you're doing things like inserting
code (for me, I'm coming up with a way to simplify entering comments into
code in an Access db).

Set up:

Tables:

tbl_ModuleNames:
Fields:
moduleName - Text - 255 - Allow Zero Length
procName - Text - 255 - Allow Zero Length

tbl_Procedures:
Fields:
moduleName - Text - 255 - Allow Zero Length
procName - Text - 255 - Allow Zero Length
startingLine - Long Integer
bodyLine - Long Integer
countOfLines - Long Integer

Queries:

PQRYAPPEND_MODULES:
INSERT INTO tbl_ModuleNames ( moduleName, procName )
VALUES ([Enter Module], [Enter Procedure]);

PQRYSELECT_PROC_NAMES:
SELECT tbl_ModuleNames.moduleName, tbl_ModuleNames.procName
FROM tbl_ModuleNames
GROUP BY tbl_ModuleNames.moduleName, tbl_ModuleNames.procName
HAVING ((Not (tbl_ModuleNames.procName) Is Null));

PQRYAPPEND_PROCEDURES:
INSERT INTO tbl_Procedures ( moduleName, procName )
SELECT PQRYSELECT_PROC_NAMES.moduleName, PQRYSELECT_PROC_NAMES.procName
FROM PQRYSELECT_PROC_NAMES;

Code (you can call this any module you want):
Option Compare Database

Public Function CheckDB()
Dim db As DAO.Database
Set db = CurrentDb
Dim accobj As Access.Application
Set accobj = New Access.Application
accobj.OpenCurrentDatabase InputBox("Enter the Database File Name",
"Load", ""), True
Debug.Print accobj.Modules.Count
Dim i As Long
i = 0
db.Execute "delete * from tbl_Procedures", dbFailOnError
db.Execute "delete * from tbl_ModuleNames", dbFailOnError
For i = 0 To accobj.Modules.Count - 1
Dim mymod As Access.Module
Set mymod = accobj.Modules(i)
Debug.Print mymod.Name & " - " & mymod.CountOfDeclarationLines & " -
" & mymod.countOfLines
Dim x As Long
x = 1
For x = 1 To mymod.countOfLines
Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("PQRYAPPEND_MODULES")
qd.Parameters("Enter Module").Value = mymod.Name
qd.Parameters("Enter Procedure").Value = mymod.ProcOfLine(x,
vbext_pk_Proc)
qd.Execute dbFailOnError
Next
Set mymod = Nothing
Next
db.QueryDefs("PQRYAPPEND_PROCEDURES").Execute dbFailOnError
DoEvents
db.TableDefs.Refresh
DoEvents
db.Execute "delete * from tbl_ModuleNames", dbFailOnError
Dim rs As DAO.Recordset
Set rs = db.TableDefs("tbl_Procedures").OpenRecordset
If rs.EOF = False Then
rs.MoveFirst
While rs.EOF = False
Dim procLine, bodyLine, countOfLines As Long
procLine =
accobj.Modules(rs.Fields(0).Value).ProcStartLine(rs.Fields(1).Value,
vbext_pk_Proc)
bodyLine =
accobj.Modules(rs.Fields(0).Value).ProcBodyLine(rs.Fields(1).Value,
vbext_pk_Proc)
countOfLines =
accobj.Modules(rs.Fields(0).Value).ProcCountLines(rs.Fields(1).Value,
vbext_pk_Proc)
rs.Edit
rs.Fields(2).Value = procLine
rs.Fields(3).Value = bodyLine
rs.Fields(4).Value = countOfLines
rs.Update
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
accobj.Quit
db.Close
Set db = Nothing
End Function

The end result is a table with all of the distinct procedure names of each
module in an external database, with their starting, body, and counts of
lines. Hope this helps anyone!

 

[BlockNinja]

Thank you that worked like a charm! Almost got my comment inserting code
perfected just working on the forms. (man... when you've got some 25-30 procs
per class/module it really does come in handy)