Access 2010 audit log using table macros

[nrgins]

I'm trying to create an audit log using Access 2010 macros. I have a macro that checks a single table field to see if it's changed, and then writes a record to the audit log if it has. That works fine.

The problem is that I need it to check ALL fields in the table and, for each one that's changed, write a record to the audit log table. I don't see a way to loop through all fields in a table record and check each one to see if it's been changed. Is there a way to do that?

The code I'm using to check a single field basically goes:

If Updated("MyField") Then
Create a Record In MyLogTable
SetField (OldValue, [Old].[MyField])
SetField (NewValue, [MyTable].[MyField]
...
End If

Any help is appreciated.

Thanks!

Neil

[ssanfu]

Maybe this will help. It is VBA code not Macros. (I don't use macros)

See Allen Browne example at
http://allenbrowne.com/AppAudit.html

[June7]

Maybe not even possible with macros. Wouldn't want to try. I don't use macros either, but then never had to build web database.

[nrgins]

Am wanting to use macros to take advantage of the new table triggers in Access 2010. Once the logic is in a table's Update, Insert, and Delete triggers, changes will be written to the audit log, regardless of where the changes are made.

When putting the logic in the front end using VBA, you don't capture changes if someone makes a change directly in the back end. Also, it requires you to call the audit log from your code every time you make a change through code, instead of through the form.

Thus, while the macros aren't as easy to use as VBA, using the table triggers that are now available are a far superior method for managing an audit log.

That is why I am trying to do this with macros.

[June7]

Then I wish you luck in translating the VBA to macro because I very much doubt you will find a macro version already built.

I suppose Data Macros can call a VBA function (not a sub) procedure?

Why is anyone working directly with tables and queries?

[nrgins]

"Then I wish you luck in translating the VBA to macro because I very much doubt you will find a macro version already built."

Actually, there are many places where macro audit log code is posted on the web. I was just hoping to find a way to loop through fields. There doesn't seem to be one, meaning I have to copy the code to run it for field each time for each field. A bit of a pain; but worth it to get the superior functionality of a table-based audit log.

"I suppose Data Macros can call a VBA function (not a sub) procedure?"

Yes, but that wouldn't work here, because the VBA audit log function is based on a form's before update event. The table-based one is called after the record has been saved.

"Why is anyone working directly with tables and queries?"

Tables and queries? Did you mean tables and macros? If so, then, in addition to the reasons I gave above, the table-based audit log also catches cascading deletions and modifications. Also, while VBA code placed in a form's before update event would catch all changes made through the form, the programmer is responsible for calling the audit log directly for any changes made to the data through code. That leaves a LOT of room for error!

But when using table triggers to call your audit log code/macro, the changes are logged regardless or whether they're done in the front end or back end, and whether they're done through a form or through code. Once the macros are in place, it's done, and doesn't have to be thought about anymore.

And it's actually pretty simple. I just finished doing an audit trail sample db, with insert, delete, and update capture. As noted above, I had to copy the code once for each field and paste, changing the field name each time. A bit of a pain; but it only has to be done once. Once it's done, I don't have to think about the audit log anymore, unlike if I was doing it in VBA, and would have to make sure that every piece of code that changes data calls the audit log.

Neil

[June7]

I guess it was the looping to all fields/controls I expect you will have trouble finding macro for. I haven't done much with macros and I don't know that they can handle that. This example shows VBA to loop all the controls on a form http://www.devhut.net/2010/12/05/ms-...ols-on-a-form/

Allen Browne's functions can be called from whatever event you want, form's BeforeUpdate is logical for the AuditEditBegin function, I suppose DataMacro BeforeUpdate could call that function as well. I guess the issue is that if users are interacting directly with tables/queries then the form is not open and it is not the form and controls need to reference but the fields of the table. I have no idea if DataMacro can reference the fields in loop.

I meant tables and queries because both allow data entry/edit. Ideally, users don't touch the backend and are denied the Navigation pane.

[nrgins]

No, can't call the VBA code for reasons I noted above.

And, as noted, even with Allen Browne's code, you still have make special calls to write to the audit log whenever you directly modify data (say you open a recordset and modify data that way). The code in the form's Before Update event won't catch that.

That creates a lot of work for for the programmer AND a large source of potential error, if he forgets to put that in. With back end trigger macros, there's nothing to remember. Once it's in place, it's in place, and there's nothing else to do.

And as for users not touching the back end, yes, that's ideal. But we both know that often happens, whether it's a user, or the system administrator, or whomever. The point is you can't guarantee that someone won't change data through the back end.

Neil

[June7]

Hopefully system administrator would know better. If my users find and touch the backend I would have to shoot someone. I have the Navigation pane unavailable and no one knows about the shift key bypass. And even if they do they wouldn't dare!

Why do you need to loop through all fields if the code creates a log record that has all fields regardless of which field(s) changed?

Can only wish you luck in your effort to get the code as you want.

[nrgins]

Right - but you see, you're relying on them "not knowing" or "not daring" to use the shift key bypass. Or that your system administrator would "know better" and there wouldn't be a situation where he might need to go in and adjust some data (which he probably would at some point). I prefer to use the method that GUARANTEES the log will be written regardless of whether users "know" or "dare" or admin "knows better." I think that's a far superior approach.

As for looping through the fields, the audit log does not record the entire record. Rather, it creates a record for each field that has changed, with the record ID, the old value of the field, and the new value of the field (along with the field name, table name, etc.). In this way, one audit log can be used for all tables, rather than one audit log for each table. And, also, it's very clear which fields have changed, and what they were changed from and to. (This is, by the way, the type of log that most people use.)

As for getting the code as I want, well, I've come to realize that I can't loop through the fields in the macro. So I've accepted that I have to place a call to the macro that writes to the audit log for each field, and just copy that macro call X number of times (once for each field). It's a bit of a pain; but it only needs to be done once.

[June7]

So you want one table to serve as an audit log for all tables. That is different from Allen Browne's design. According to his tutorial "The end result is a copy of the record in the audit log, stamped with username, date and time, and type of operation (deletion, insertion, or edit)." This means to me that every table that needs edits tracked would have a corresponding log table.

[nrgins]

Well, that's one way to do it. Like I said, most people prefer the one-log-table model. It's cleaner, more efficient, only writes data that's changed, instead of all data (and, thus, takes up MUCH less space, and allows you to easily see which fields have been changed (since they're the only ones that will be written to the log table), with their before and after value. It's a great design, and it's what is the norm these days - at least in my experience. I haven't met anyone (until now) who uses the one-log-table-per-table approach. But, hey, if you're happy with it, then great! :-)

[June7]

Never really thought about, never implemented edit log. Allen Browne is only example I've looked at. There are always trade-offs and as you say - if you're happy ...

[Mark@SF]

I read your post and thought you might be able to help since it looks like you are using the BeforeDelete data macro to capture a to-be-deleted record's information in an audit trail.

I have an Access 2010 back-end db with several tables. Each table in my db uses its AfterInsert and AfterUpdate event-driven macros ("data triggers") to capture information (table inserts and field changes) and send that information to a named macro in the "Change Log" table. The named macro then creates a new record (audit trail) in the Change Log table. This all is working well.

I have not been able to figure out how to use the BeforeDelete data macro on a table in my back-end db to capture the to-be-deleted record's information (e.g., its primary key and one or more field values) and then send that information to the Change Log's named macro which would then create a new record in the Change Log table with the deleted record's information.

The drop-down list of available Access 2010 macro commands for the BeforeDelete data macro does not include the RunDataMacro command (like the AfterInsert and AfterUpdate data macros). The AfterDelete data macro is not an option because the record has already been delete by the time the macro is "fired" by the record deletion event.

So, I have instead resorted to code in the application's front-end UI (a VB.Net project) to insert records into the back-end db's Change Log table before a record is deleted. As you might imagine, this complicates things (e.g., cascaded record deletions via referential integrity rules can't happen -- instead I must perform each record deletion one-by-one to record the audit trail information for each to-be-deleted record).

Am I missing something here? How have you set-up your macros to capture record deletion event information in your audit trail?