3

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.

R3uK
  • 14,417
  • 7
  • 43
  • 77
Joshua Honig
  • 12,925
  • 8
  • 53
  • 75

1 Answers1

4

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.

sarh
  • 6,371
  • 4
  • 25
  • 29
Scotch
  • 3,186
  • 11
  • 35
  • 50
  • 1
    Did you try that method to list Data Macros (new in Access 2010), as opposed to "regular" Macros? I did, and it didn't work for me.... – Gord Thompson Apr 27 '13 at 07:40
  • @GordThompson Actually, I just tried it with standard macros. I just found some code for the data macros, will update my answer with what I found. – Scotch Apr 27 '13 at 08:06
  • 1
    @Scotch : Rewrite your answer to just address Data Macros (that's the question, not normal macros) and I'll mark it as the answer. For future readers it's confusing to have the content be wrong but the link be right – Joshua Honig Apr 29 '13 at 12:38
  • @JoshuaHonig Edited to include the datamacro code instead of macro. – Scotch Apr 29 '13 at 16:20
  • This answer is useful. But could someone explain what the LvExtra field contains and *why* the query ("s" variable) works? I looked at the contents in my database, and all I see is "Long Binary Data." A web search indicates that this system table field is "undocumented." – EJ Mak Aug 02 '15 at 21:26
  • Very useful but just faced with an issue that LvExtra can still be populated when data macro was deleted. Currently I don't see its content I just see that in this case it has lower size, in my example it is 34 bytes versus hundreds of bytes for a valid table with data macro. Not sure, but it looks like it is just data macros xml with no nested items. SaveAsText of acTableDataMacro fails on such table and the only option I see is just to handle this failure. – sarh Aug 28 '23 at 14:18