Best use of Macros (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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.

 

Attachments

Last edited:

Mike Krailo

New member
Local time
Today, 11:51
Joined
Mar 28, 2020
Messages
19
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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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

New member
Local time
Today, 11:51
Joined
Mar 28, 2020
Messages
19
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.
1589199699210.png

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

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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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
Code:
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.
 

Attachments

Last edited:

Mike Krailo

New member
Local time
Today, 11:51
Joined
Mar 28, 2020
Messages
19
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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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.
 

pisorsisaac@gmail.co

Active member
Local time
Today, 08:51
Joined
Mar 14, 2017
Messages
361
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

CID Moderator
Staff member
Local time
Today, 16:51
Joined
Jan 14, 2017
Messages
13,149
@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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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

New member
Local time
Today, 11:51
Joined
Mar 28, 2020
Messages
19
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.

Code:
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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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

Active member
Local time
Today, 10:51
Joined
Jan 30, 2020
Messages
586
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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
13,090
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom