Is it possible to programmatically enumerate the Data Macros in an Access 2010+ database? If so, how?
Note: Data Macros are trigger-like procedures that are created in the context of the table designer UI. They were new in Acces 2010. They are NOT the same thing as normal macros, which are easy to enumerate.
They have their own new AcObjectType
enumeration value : acTableDataMacro
, but I can find no other aspect of the Access or DAO object model that refers to them. They do not even appear in the MSysObjects
table.
This code will export DataMacro metadata to an XML Document (Source):
Sub DocumentDataMacros()
'loop through all tables with data macros
'write data macros to external files
'open folder with files when done
' click HERE
' press F5 to Run!
' Crystal
' April 2010
On Error GoTo Proc_Err
' declare variables
Dim db As DAO.Database _
, r As DAO.Recordset
Dim sPath As String _
, sPathFile As String _
, s As String
' assign variables
Set db = CurrentDb
sPath = CurrentProject.Path & "\"
s = "SELECT [Name] FROM MSysObjects WHERE Not IsNull(LvExtra) and Type =1"
Set r = db.OpenRecordset(s, dbOpenSnapshot)
' loop through all records until the end
Do While Not r.EOF
sPathFile = sPath & r!Name & "_DataMacros.xml"
'Big thanks to Wayne Phillips for figuring out how to do this!
SaveAsText acTableDataMacro, r!Name, sPathFile
'have not tested SaveAsAXL -- please share information if you do
r.MoveNext
Loop
' give user a message
MsgBox "Done documenting data macros for " & r.RecordCount & " tables ", , "Done"
Application.FollowHyperlink CurrentProject.Path
Proc_Exit:
' close and release object variables
If Not r Is Nothing Then
r.Close
Set r = Nothing
End If
Set db = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " DocumentDataMacros"
Resume Proc_Exit
Resume
End Sub
EDIT: Gord pointed out that you wanted the DataMacros opposed to standard macros. I found some code and tested it (it works) here
I tested the top function when you follow that link and it saves information regarding your table macros for each table in an XML document. It works nicely, props to whoever wrote it.