Documenting Access macros

Access contains a tool to document parts of a database, including the macros. The program generates a long report with lots of blank spaces and possibly unwanted information.

The program described below will read each of the macros within the current Access database and identify the actions and parameters on each line. The results are then written to a table.

It does not attempt to interpret the meanings of some of the various parameters which are stored as arguments for each macro action. It is only attempting to identify the main arguments, such as the names of forms and queries.

Any conditions placed upon macro lines are shown within square brackets. In the first macro ('temp test condition') there is a condition that the user defined function LookupKeyData(10) must return a value of 1. The 3 dots [...] indicate that the same condition is applied to the next line.

The output then shows the action (e.g. OpenQuery) and the target object (inside 'apostrophe' marks). If the macro line has a user comment, this is shown within {curly braces}.

If the results are displayed in a query rather than the actual table, a criteria can be added to exclude actions starting with actions such as SetWarnings or Echo. These are not critical to explaining the purpose of the macro.

Sub AllMacros() ' The contents of ALL macros will be appended to a table Dim Obj As AccessObject, dBs As Object, MyCondition As String Dim mName As String, MyCount As Integer, MyStr As String Dim MyDb As Database, MySet As Recordset, MyParamCount As Integer Dim MyAction As String, MyComment As String, MyParam As String Set MyDb = CurrentDb() Set MySet = MyDb.OpenRecordset("temp_text", dbOpenDynaset) ' Temp_Text is a table containing a wide text field called [Chars]. ' If an appropriate table does not exist, it must be created or the ' program changed to use another similar table and field name. MySet.MoveFirst ' delete all existing records in the Temp_Text table Do While Not MySet.EOF MySet.Delete MySet.MoveNext Loop Set dBs = Application.CurrentProject For Each Obj In dBs.AllMacros ' loop through each of the macros mName = Obj.Name MySet.AddNew MySet!chars = "Macro: " & mName MySet.Update ' The contents of each macro are temporarily saved as a text file. ' The name and location of the text file can be edited and changed. SaveAsText acMacro, mName, "C:\MyMacro.txt" Open "C:\MyMacro.txt" For Input As #1 Do Until EOF(1) ' read the contents of each temporary text file Input #1, MyStr MyStr = Trim(MyStr) If MyStr = "Begin" Then MyAction = "" MyCondition = "" MyComment = "" MyParam = "" MyParamCount = 0 End If MyStr = Left(MyStr, Len(MyStr) - 1) ' remove final speech marks If Left(MyStr, 6) = "Action" Then MyAction = Mid(MyStr, 10, 100) If Left(MyStr, 6) = "Commen" Then MyComment = " {" & Mid(MyStr, 11, 100) & "}" If Left(MyStr, 6) = "Condit" Then MyCondition = "[" & Mid(MyStr, 13, 100) & "] " If Left(MyStr, 6) = "Argume" And MyParamCount = 0 Then If MyAction = "Hourglass" Or MyAction = "Setwarnings" Then ' add a more meaningful parameter narrative to these actions If Right(MyStr, 1) = "0" Then MyParam = "Off" MyParamCount = 1 End If If Right(MyStr, 2) = "-1" Then MyParam = "On" MyParamCount = 1 End If Else ' add the parameter narrative if it contains more than 2 characters If Len(MyStr) > 13 Then MyParam = Mid(MyStr, 12, 100) MyParamCount = 1 End If End If End If If MyStr = "En" Then ' append details of the current macro MySet.AddNew MySet!chars = MyCondition & MyAction & " '" & MyParam & "'" & MyComment MySet.Update End If Loop Close #1 ' close the temporary text file MySet.AddNew MySet!chars = "" MySet.Update Next Obj ' review the next macro MySet.Close Debug.Print Time(), "AllMacros(): Macros added to Temp_Text" End Sub

Access 2010 VBA Programming   more ...   more ...

file: Acc_Macro.htm

Page last updated Apr14

© MeadInKent.co.uk 2014