Best use of Macros

[jdraw]

Here's a sample database (Acc2010) using table data macros. It is part of the dabbling/learning that I did earlier. I've added a few forms for context setting.
This is my only use of macros.

 

[Mike Krailo]

I know this is an old topic but wanted to thank jdraw for that awesome demo on data macro's. It had my head spinning at first but I finally got all the auditing to work in my application because of that demo. Just wish there was a way to do the table level triggers in VBA instead. That macro interface is so painful to work with.

 

[jdraw]

Mike,
Thanks for the feedback. I'm glad the demo was useful for you. There is very little info on Data Macros.
It might be helpful to others if you posted some specifics from your application and described your usage/experience with them.

 

[Mike Krailo]

The application is tracking hardware that is moved or replaced in a highly specialized system of components. I recently learned how to use transactions to handle all the location and status updates to the record changes that occur with normal workflow and my form based auditing didn't work any more. As soon as I stumbled on this thread, I spent the weekend experimenting and learning how the demo code worked. I used the same table structure for the auditing table as in the example you provided so not much different than what's already in the demo. Just needed to audit the main table of hardware items which has six fields monitored for the Create/Update/Delete actions and other than being a pain to setup, everything worked perfectly after testing. These data macro's are perfect for auditing purposes.

Here is a screenshot of my old History form which allowed me to filter on just one serial number and see it's overall history.

I haven't made the form yet for the new table (Trans2) but you will probably recognize the structure in the table.

BTW, I see in your demo that the XML file can be saved to a file but how do you import it back into another database? I would hate to have to go through all those steps setting it up again.

 

[jdraw]

It's been a few years since I've looked at this.
I haven't tried copying the XML file, nor the named macro generally, to another database.
In the macWriteAudit, there is a note showing how you can save the macro as a text/xml file.
Here's a sample from today --using the immediate window

SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\jack\documents\macWriteAuditRec.txt"

This gives this result:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Name="macWriteAuditRec"><Parameters><Parameter Name="parmTableName" Description="name of table where action occurred"/><Parameter Name="parmFieldName" Description="name of field in table where action occurred"/><Parameter Name="parmActionCode" Description="action being logged CREATE, UPDATE, DELETE"/><Parameter Name="parmOldValue" Description="old value of Field"/><Parameter Name="parmNewValue" Description="new value of Field"/><Parameter Name="parmChangedBy" Description="logon id of user who actioned this change"/><Parameter Name="parmChangedDate" Description="Timestamp of this action Now()"/><Parameter Name="parmRecordID" Description="RecordID of record being actioned (the PK-- this was an experiment)"/></Parameters><Statements><Comment>Named Macro to write audit log info into tblAuditLog 13-May-2017 NOTE:: You can copy a named macro via the immediate window using SaveAsText acTableDataMacro, "youtTableName", "Your directory and Filename and extension" My example: SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\mellon\documents\macWriteAuditRec.txt" and you can print the xml tree formatted using Notepad++ with XML plugin.</Comment><CreateRecord><Data><Reference>tblAuditLog</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">TableName</Argument><Argument Name="Value">[parmTableName]</Argument></Action><Action Name="SetField"><Argument Name="Field">FieldName</Argument><Argument Name="Value">[parmFieldName]</Argument></Action><Action Name="SetField"><Argument Name="Field">ActionCode</Argument><Argument Name="Value">[parmActionCode]</Argument></Action><Action Name="SetField"><Argument Name="Field">OldValue</Argument><Argument Name="Value">[parmOldValue]</Argument></Action><Action Name="SetField"><Argument Name="Field">NewValue</Argument><Argument Name="Value">[parmNewValue]</Argument></Action><Action Name="SetField"><Argument Name="Field">ChangedBy</Argument><Argument Name="Value">[parmChangedBy]</Argument></Action><Action Name="SetField"><Argument Name="Field">ChangedDate</Argument><Argument Name="Value">[parmChangedDate]</Argument></Action><Action Name="SetField"><Argument Name="Field">RecordID</Argument><Argument Name="Value">[parmRecordID]</Argument></Action></Statements></CreateRecord></Statements></DataMacro></DataMacros>

But you can get a "prettier" format by using Notepad++ (free tool) and use the XML plugins.
Here is part of the output (from a prtSc, to show the setup) using the XML Tools plugin to Notepad++.

You may be able to LoadFromText, but I haven't experimented with that. Here are some tips related to LoadFromText from HiTechCoach.

Good luck.

 

[Mike Krailo]

OK, would have been easier if there was a way to import that XML file back into another version of the table in a different database. I just imported the new auditing table into the other database and the data macro came along with it so the XML must be embedded in the TableDefs itself.

I'll Just make a backup of my data in the main Items table and import the whole table. Then go back and update any changes to the imported table with a few queries.

I'm just trying to update my live copy of the database from the development version which has lot's of changes.

 

[jdraw]

Well you're into a new territory here. Make sure you backup your database "often". Keep a record of the steps you've taken so you can get back to some known point when/if needed. I could not find any details of the data macro's storage location etc. It may be buried in some system table???
I'd be cautious doing anything with your live database until you have a working process in a development set up.
In overview you have insert/update/delete macros for each table, and the named macro to write the record to the audit table. I'm not an XML person, but there may be someone who can decipher and/or write the XML. I know Jleach did his own as per his post #19 in this thread.
Did you try the LoadFromText?
Also note that any info you discover will be new or at least not readily available via google etc. So any details you can add to this thread could be useful to others.

 

[Isaac]

I have never used a macro and wouldn't touch them. Everything can be done in events. I've never had a need for AutoExec.

 

[isladogs]

@psiorisaac@gmail.co
As I said in post #4, I never use macros apart from Autoexec and Autokeys.
However if you can explain how to replicate the functionality of an autokeys macro using an event procedure, I'd be pleased to hear it.
In addition, certain 'safe' code can be run in an autoexec macro before a project has been marked as trusted. There is no way of doing so using VBA as that can't run until the project is trusted

 

[jdraw]

Further to Colin's comments, have you created an Audit table to record each and every change made to your tables. The approach using Forms and form events can be quite cumbersome and does not deal with changes made with direct table interaction or queries. If you have a procedure to audit changes to tables regardless of how those changes were invoked, I'm sure many would like to hear more.

 

[Mike Krailo]

Jdraw, I did some more testing on this and have had real good luck with the SaveToText and LoadFromText commands from the immediate window. I essentially was able to transfer the data macros from each of the two tables using the SaveToText and then on the other database with similar structure but no data, used LoadFromText to apply the same data macro.

SaveAsText acTableDataMacro, "Items", "C:\Users\mike\Path\To\Your\File\Items_DataMacro.xml"
SaveAsText acTableDataMacro, "Trans2", "C:\Users\mike\Path\To\Your\File\Trans2_DataMacro.xml"

Then on the production database:
LoadFromText acTableDataMacro, "Items", "C:\Users\mike\Path\To\Your\File\Items_DataMacro.xml"
LoadFromText acTableDataMacro, "Trans2", "C:\Users\mike\Path\To\Your\File\Trans2_DataMacro.xml"

Then I just appended the data from the production version of the Items table to the new table and everything has been working correctly. I'm using Office 365 64-bit version.

Haven't tested it yet but I'll bet I could have just skipped doing the append and do the LoadFromText directly on my production Items table. The tabledefs object for each table is separate from the data macro after exporting each of those objects and looking them over. But they are obviously linked somehow, just can't see it in the exported files.

 

[jdraw]

Good stuff. Don't overlook the importance of a database backup- just in case.
Also, if you can attach a database showing your logic and code it could be useful to others.
Continued good luck with your project.

 

[zeroaccess]

I just went into Data Macros and the interface looks the same as any other macro.

In that case, you should be able to just click in the white space, then Ctrl + A to select all, and Ctrl + C to copy. Then, open a new one and Ctrl + V.

You can paste it elsewhere to see the XML

 

[jdraw]

Yes, copy and paste the xml to see it was posted by jleach in post #19.
I think Mike is trying to copy a data macro to a different database without reinventing/recoding the whole thing. If you have a working sample to show the steps involved for this, I'm sure others would find it useful.

 

[zeroaccess]

All you need to do is open a new macro and hit Ctrl + V.

 

[NauticalGent]

Second, the database engine does the work, so the front end is freed up faster, and there's less network traffic.

Paul, I had the same idea when I had read that the macro gets "handed off" to the the DB Engine, not JET/ACE - but wonder what kind of performance increase we are talking about. Maybe Colin could see fit to grace us with another Speed Comparison/Myth Buster thread...?

 

[NauticalGent]

Spoiler: Jacks Post

It's been a few years since I've looked at this.
I haven't tried copying the XML file, nor the named macro generally, to another database.
In the macWriteAudit, there is a note showing how you can save the macro as a text/xml file.
Here's a sample from today --using the immediate window

SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\jack\documents\macWriteAuditRec.txt"

This gives this result:


But you can get a "prettier" format by using Notepad++ (free tool) and use the XML plugins.
Here is part of the output (from a prtSc, to show the setup) using the XML Tools plugin to Notepad++.

You may be able to LoadFromText, but I haven't experimented with that. Here are some tips related to LoadFromText from HiTechCoach.

Good luck.

Jack, I've got to tell you, your demo is probably the best "tutorial" out there that adequately shows how/when/why to use these. Great work and thank you.

 

[ebs17]

@jdraw:
I have never seen a comparatively good job anywhere like the demo database provided, especially since, as I said, there is very little information about it anyway.
Many compliments and many thanks for your work.

Regarding the transfer to other access files:

Application.SaveAsText ...
Application.LoadFromText ...

... are methods of the access object. Application refers to the current object. To transfer you need the reference to the second access file:

Dim oAcc As Access.Application
Set oAcc = CreateObject("Access.Application")
With oAcc
    .OpenCurrentDatabase "c:\AnyDirectory\AnyFile.accdb"
    ' .LoadFromText ...
    .CloseCurrentDatabase
End With
oAcc.Quit
Set oAcc = Nothing

 

[pbaldy]

Paul, I had the same idea when I had read that the macro gets "handed off" to the the DB Engine, not JET/ACE - but wonder what kind of performance increase we are talking about. Maybe Colin could see fit to grace us with another Speed Comparison/Myth Buster thread...?

It would be interesting to see a test, though keep in mind when I made that comment I was referring to triggers (SQL Server in my case), not data macros. I guess theoretically the data macro is running on the machine with the back end on it, it may see similar gains. I guess the comparison would have to be FE code doing the additional inserts/whatever vs a data macro/trigger.

 

[NauticalGent]

Colin/Jack any thoughts on doing speed trials on this?