[AccessD] Audit Trail Suggestions

[David Emerson]

Thanks Don for the suggestion.  In my case I am not expecting too many
changes so a single record per field will suffice.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
McGillivray, Don
Sent: Tuesday, 17 March 2015 4:51 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Audit Trail Suggestions

A little late to the party, but thought I'd chime in with an approach that
I've encountered.

At my current job we use an off-the-shelf product based on an Oracle
backend.  The application layer implements optional audit tracking,
selectable at a data entity level.  But instead of writing a record to the
audit table for every single column value change, it summarizes all the
changes that occurred in each update event, concatenating the results into a
specially delimited string.  Separate fields capture the ID of the affected
record, the user ID, and date, but all the change information is embedded in
a single (admittedly hideous) value.  In the case of our application, each
value in the string is preceded by an asterisk followed by a value
indicating the length of the value to follow, followed by the value itself,
with column names, old values, and new values presented in pairs in a
predictable sequence.  Something like (air code here):

*#ColumnName1*#ColumnName2*#OldValue1*#OldValue2*#NewValue1*#NewValue2

There may be some other data at the head of the string, such as column count
or some such, but you get the idea.

This strategy represents a shift from a one column/one record approach that
was originally used with the product, and presumably was implemented to
reduce the number of records required to house audit data in large scale
implementations.  If you anticipate a large volume of audit data as the
result of your audit tracking, it may be worth considering a similar
strategy.  Of course, you'd need to build the secret decoder ring to
translate the arcane delimited string into something human readable, and
there's processing overhead associated with that, and I'll tell you from
experience that writing useful queries against that delimited string is a
total PITA.  Still, folks a lot smarter than me came up with the idea, so
something like it may be worth considering, depending on the circumstances.




-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Friday, March 13, 2015 9:22 PM
To: AccessD
Subject: [AccessD] Audit Trail Suggestions

Hi Team,

Looking for ideas for implementation.

Access 2010 FE, SQL 2008 R2

A client wants to keep track of some fields in some tables when they are
changed.  There are over 50 tables involved and anywhere from 3 to 30 fields
per table.  The tables and fields are predefined.  Most of the data changes
will be done via Access screens but there are some fields that are changed
through code

When a field value is changed they want to store in a log table date, person
making change, old value, new value.

Looking for ideas of how others might have tackled this type of problem
before.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com