Data Macro Limitations

[jjsande1492]

Hi, everyone.

I am recording changes made in a table and logging them in another table. Originally, I was using VBA to do this, but data macros seem to be a better option with less overhead. My issue is that I understand a data macro cannot have more than 999 actions in it. My table has 122 fields (please no normalisation arguments on the number of fields in my table - I'm well aware). Each field would need 10 actions to update my change log table. Some of these actions are recording the same data regardless of the field being changed (e.g. the date/time, the user making the change, etc.). I'm looking for a way around the 999 action limit.


Can I use a data macro to look for a change in any field and input this common data to cut down on my number of actions? I tried "If Updated(*)", but Access is looking for a particular field so it gave me an error.

Or...


Can a data macro have variable fields? In other words, can the data macro automatically detect the changed field and insert the old and new values without me having to create actions for each individual field?



Thank you.

[Micron]

is this https://www.accessforums.net/showthread.php?t=72028 the same issue? Splitting an issue over 2 or more threads isn't a great idea.

I'm not sure I understand the issue. The macro can fire simply by changing or adding to the records in the table so I don't get what 10 actions in any one field means. You only have 5 events (actions?) available to you.

look for a change in any field

This can mean if any field is changed (thus simply the record) or maybe to you it means "know which field" and maybe even "also know what the change is".

If you want to know which field(s) in a table of 122 fields and
- know what the field was updated to only if different or
- know what the field was updated to even if it's the same,
then I can't envision data macros being suitable for this. That would be an incredible level of auditing (given the number of fields) to handle via data macros or even code. Normally, only some of the fields are of concern.

Perhaps if you are not dealing with a really large record set in your form, then in code you could set a module level variable to true if any change is ever made to any record. Then when closing the form or navigating to another record, you could write the entire 122 field record to tblAudits. By joining the 2 tables on the PK and using DISTINCTROW predicate, you could possibly return records where any field was different although I don't know how you could single out which field(s) without reading through all of them.

Without being able to play with your db, I'm just scratching at the surface. Possibly on the right track, but it may require doing things in steps - like first only returning one output field (the PK?) and using that to extract which records are unique by way of having been (or not, can't tell which) changed. This seems sketchy no doubt, but I can't tell if you'd have records in both tables that are exactly the same because values were re-entered. That could make them identical. To prevent that, I can only see a code solution - maybe getting the exact form control being altered upon a change in a record (using BeforeUpdate), and comparing its OldValue to the current value. If not the same, it's a change and write the record to the audit table.. If they are the same, why bother capturing that? To know which events would come in to play is impossible to say without knowing much more about the form design. If considering a code solution, you would want to be careful about which events will provide the desired result, as there are form and control events and it pays to know their inter-relationships and orders. See https://support.office.com/en-us/art...86553682f9#bm2

[jjsande1492]

Thanks for the reply, Micron. I started a new thread because while linked to the original question, it became a new question and I thought it warranted breaking out. Apologies if that was misguided.

My main table has 122 fields in it which are various information and data points on a particular part in the business. My form is a datasheet of all these fields with as much validation as could be used.

I can use data macros and the After Update event to audit changes to the main table. They also nicely handle changes to multiple records and fields at once via copy/paste from the user which was what my original question was about), but to set an event on a field requires 10 actions (assuming my definition of an action is correct) for the one field:

1. If Updated (Field)
2. Create record in audit table
3. SetField - Action
4. SetField - Date/Time
5. SetField - User
6. SetField - Changed ID
7. SetField - Changed Name
8. SetField - Field Changed
9. SetField - Changed From
10. SetField - Changed To

Given the 122 fields, this eclipses the 999 action limit of a data macro. I was looking for a way within the data macro (perhaps a named macro) to detect the field changed and write to the audit table with associated old and new values. The end goal being 10 actions for the entirety of the 122 field table. My fear is that this can't happen and I need to set actions against each field individually in which case I'm looking for a more compact way of accomplishing this. Your idea of writing the entire 122 field record to another table and looking for differences to the main table is an interesting one. I might look into that if no magical solution exists within data macros. I'm just hoping there is.

[jjsande1492]

Quick update.

I've improved the situation, but it's still not ideal. I've created an After Update data macro which passes the field name, old value, and new value as parameters to a named macro. So, where the list was the 10 items I listed previously, it is now 2:

I don't know if the actions performed in the named macro count towards the 999 action limit in the After Update event though. Any alternative ideas out there would still be greatly appreciated.

[Micron]

My suggestion would be to mark the other thread as solved, otherwise, you may continue to get responses in both places, and I think we're all on the same page with not appreciating working on an issue only to find it was solved elsewhere.

I confess to knowing little about data macros due to the inherent limitations for the normal 'run of the mill' macro. I had to play with one just to get a feel for it. That's why I'm not sure of your statements. It seemed to me that the macro applied to the table and not any particular field, so if you're able to do it at the field level, then you know more about them than me.

The major inherent limitation for macros has always been that it was never possible to incorporate error handling. When they erred out, they stopped, which could potentially leave you with partial or even no updates or appends at worst, or forms/reports that users didn't know how to get working again at the least. Thus the only macro that I (and probably my esteemed peers here) would entertain using is an AutoExec macro.

As for any other suggestions, all the foregoing leaves me with only one - vba code. Here's just one approach https://www.fontstuff.com/access/acctut21.htm
you could probably find lots more if you research. If you adopt that approach, I would note 2 things:
- I'm pretty sure you can select all of your 122 controls and apply the appropriate tag value all at once.
- I don't see the need for ADO. M$ intended to deprecate DAO but that plan seems to have gone by the wayside. Can't remember why; just thinking that for this exercise I'd stick with what I know - DAO. You would need to reference it in your db should you use it.

Suggest you read the article for some pointers, main one being to decide exactly what is an auditable change (edit only, or append/delete as well?). Keeping old field data for a record that was deleted seems a bit odd to me. If you can't trust users to make the correct decisions according to their profile, then maybe you also need user levels so that some can create and edit but not delete. That's another subject.

EDIT: forgot to mention that the example only works with bound controls. Not sure, but I think that little nugget was left out. The OldValue property does not apply to unbound controls.

[orange]

@jjsande1492,
A few thoughts:
-did you look at the example database with table data macros I gave in previous post/thread
-that is my only experience with named and table data macros ( I only had a few tables and fields to learn/experience the process(es)).
-you can use vba with forms /form events to create an audit log---but beware the sample provided by Martin Green (fontstuff) does not record the correct record and we could not get it to work with subforms. See the dialog in this thread with PSSMargaret
-I'm not convinced that your table design with 122 fields is correct(but I don't know your application) ---I haven't seen it in a relational table (possibly in a spreadsheet)
-what is the probability of all 122 fields being updated at 1 time --sounds like it should be a delete and insert new record?
-some of the audit logs only identify a new/edited or deleted record (not a field new/old value level)

[ssanfu]

Just in case you want to read more about Audit Logs, See Creating an Audit Log by Allen Browne

Re Macros: I'm with Micron on this issue. I have never used a macro in Access.... they are too limiting.

[jjsande1492]

Thank you for the replies, everyone.

@Micron / @ssanfu - I'm pretty handy with VBA (though not godly) and I originally wrote a very good VBA auditing solution. The only thing stopping me was that my form is a datasheet and when a user copies/pastes data across multiple fields or records (which I want them to be able to do), the VBA couldn't handle that and erred out which is what my other post was about. Data macros were suggested and I like that the advantages pbaldy lists from the link @orange posted: "Two advantages come to mind. First, there's no way a user can find some obscure way to get around VBA code to accomplish those inserts. Second, the database engine does the work, so the front end is freed up faster, and there's less network traffic. Maybe that's three advantages."

@orange - Yes, I read that thread with great interest. It reinforced the notion that data macros were the way to go. It looks like the example given still referenced an individual field unless I am misreading it. This means I need to individually test all fields in the table to see if they were updated in order to pass the appropriate parameters (field name, old field value, new field value) to the named macro. I was hoping there was a solution within data macros where it could dynamically detect the field that was changed and pass the field name and associated old and new values to the named macro.

For reference, the application of this database / audit log is bringing new parts into our business to on-sell. Each part has 122 points of data and information associated with it. The requirements are to let the users have great flexibility with interacting with said data (basic validation still happens - numerical fields, formatting, etc), but when a piece of data has been determined to be wrong, an audit trail is needed to be able to see what the user changed it from and to. The audit should (and does currently) capture inserted and deleted records in the 'After Insert' and 'After Delete' events.

With VBA, it is easy to identify the changed field and get its old and new values without a static reference to the field name. I was chasing something similar with a data macro. Since I can't find one, I have 122 of these in the After Update event:

The named macro being called is:

If this is as good as it can be, then so be it, but it sure would be nice if I could have one piece of code that identifies the field and associated old and new values. Then again, maybe you showed it to me @orange and I couldn't decipher it. Thanks again for your help.

[Micron]

AFAIK, pasting multiple records into a form datasheet should cause applicable form events to be called each time, and in turn, the applicable field events. Thus it seems it ought to be doable, but at some point, might require a short wait interval between one or more events. As I mentioned, macro experts seem to be in short supply around here, and since I'm not one of them, I cannot offer any more help on the subject. Maybe somebody who's been holding back will chime in. Failing that, posting your procedure(s) for your attempts might result in some help. You could also post a zipped sample db complete with any forms or tables needed to support any development help.

You would have to exactly specify what constitutes an auditable action because record appends or deletes are not always considered in any audit trail.

[orange]

@jjsande1492

If this is as good as it can be, then so be it, but it sure would be nice if I could have one piece of code that identifies the field and associated old and new values. Then again, maybe you showed it to me @orange and I couldn't decipher it.

I think that's as good as it is. The table data macro is triggered when one of the identified table's events occurs --regardless of the origin. If you have data macros for a table, then whether it is direct editing of a table record value, or a query or vba -the data macros is triggered. It's up to you/your code to identify what fields and values from the record should be recorded/logged.

You could write some vba that parsed your table design using Fields property {Field(0)---Field(x)} and even concoct some "mocked up macro code" but I don't see a way to put such output into a data macro. Note: that my use of data macros is limited to my sample database.

I would like to hear/see more about your requirement -preferably a small database with a few mocked up records. I have never seen a relational database table with 122 fields. Perhaps not all changes to the 122 fields are required to be logged??

Since many issues with database can be traced back to design, readers would like to know more about the application and the table design that has 122 fields. That's what I would investigate because it seems rare, and because it seems to hit an Access data macro limitation. My question is what is special about your design that precludes the use of the data macro features in Access.

Good luck.

[jjsande1492]

Thanks, orange. I won't paint myself as an Access guru. My Excel skills are good which is why I'm competent with VBA, but I'm still learning the ins and outs of databases. Long, long ago I did a fair bit of work in PHP with MySQL, but it has pretty much all left me since then. I've only been using Access for two months now. I had a good think about the design last night and I think you're probably right in that there's some tweaking to be done on the design side, but how much, I don't know. I won't waste anyone's time here with trying to give me a tutorial on normalization. I'll try to do some reading up on it.

Thanks again for your help.

[orange]

No problem, but people here will help as best they can.
It's often a good practice to show readers some sample data so they have a better appreciation of your environment and set up.
It gives readers something to test/try/experiment with.
I have not found much info on table data macros; I did some investigation and built a database, then posted my approach and results as a demo to share with others.
Good luck.

[ssanfu]

Did I hear Normalization???

Have a look at these:

What is Normalization?
=======================
Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


and maybe

The Normal Forms
=========================
Introduction http://rogersaccessblog.blogspot.com...roduction.html
First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...rmal-form.html
Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html



Tutorials to work through

[jjsande1492]

Thanks ssanfu. I'll definitely read through these.

In my quest for simplification of macros, I've discovered that my method of calling a named macro in the After Update event causes the audit to only capture the last edited field in the record.

For example:

This is my After Update event where I call a named macro for every field in the table:

Here is the named macro:

What I'm seeing is that if I alter multiple fields within a record, the After Update event fires and only passes the parameters to the named macro of the last field edited. The After Update event only fires when you select another record and all changes made except the last change are ignored. Since there is no ForEachField function available in macros, it seems that I cannot call a named macro from the After Update event for this purpose. That is very disappointing.

[orange]

That is not what I found in my sample database. If you want to write an audit record for each field in the table (record), then you will have to write such records individually. The link I provided includes the sample database that uses table data macros and named data macro.
There is a named data macro "macWriteAuditRec".
When it is called with a number of parameters, it writes a record to tblAuditLog.

If you change a Student's FirstName and LastName, you will get 2 auditlog records ---one for each field.

Here is a link to info Database Design includes relational database concepts, normalization, analysis, vba.....

Good luck.

I'd still like to see some sample data.