| Feedback | 20 things to do with an Access Query |

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.

2 MacroLines: Select query
Macro: temp test condition
[LookupKeyData(10)=1] OpenQuery 'Audit_Log_recent'
[...] OpenQuery 'Audit_Last3'
MsgBox 'finished'
Macro: Update TempBatchRef
SetWarnings 'Off'
OpenQuery 'Delete Temp BatchRef' {delete Temp Batch Ref}
SetWarnings 'On'
OpenQuery 'BatchSelectionStatus1' {open list of completed batches (status 1)}
OpenQuery 'Temp BatchRef List' {open temp BatchRef list to be manually edited}
MsgBox 'Enter a list of Batch reference numbers to be processed'
The output from the program is appended to a table

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

Set dBs = Application.CurrentProject
For Each Obj In dBs.AllMacros ' loop through each of the macros
mName = Obj.Name

MySet!chars = "Macro: " & mName

' 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

' 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!chars = MyCondition & MyAction & " '" & MyParam & "'" & MyComment
End If

Close #1 ' close the temporary text file
MySet!chars = ""

Next Obj ' review the next macro
Debug.Print Time(), "AllMacros(): Macros added to Temp_Text"
End Sub

Access 2010 VBA Programming

UK more ...   USA more ...

file: Acc_Macro.htmPage last updated Apr14 2014 View a selection of recommended books on Access