MS Access Audit Trail

[SUnderwood]

Dear All,

I need to implement an audit trail mechanism into an Access 97 database, a function I know is not native to its operation. I've been delaying the idea for a few months because of the MAJOR recoding I envisage to implement it.

The audit-trail need only be simple, for each field in a record showing its previous value and its new value, the date and by whom it was requested to be stored in a string field, in say a table called [audit].

If anyone has experience implementing such a beast I'd be grateful to hear your suggestions.

Many thanks

Sean Underwood

 

[JeremyNYC]

Michael,

I would think that if you're going to go that far, you would just want to make unbound forms. Then it would be no problem to do all of the work inside a transaction.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[RickSpr]

Yes, but unbound forms (or Michael's approach, for that matter) present their own problems. For one, Undo doesn't work as expected, and you can't simulate it because unbound controls seem to always have the same value for Value and OldValue. So your user has less convenient editing.

For another, Undo Saved Record won't work, because as far as Access is concerned, no update occurred.

Record Locking (the form property) just gets in your way, and you have to handle any conflicts yourself (including the complexities that can arise if other users are using a different locking strategy).

If you do have a locking conflict in a multiuser environment, you either can't tell your user who the conflict is with, or you have to implement that ugly (read: unsupported) code with the magic numbers in it that looks at the lockfile and lock bytes in the back end database.

Heck, if I have to give up Access' record management, I might as well write it in VB. I'd have to do more work on the UI stuff, but at least I wouldn't have to have to worry about how Access is going to react to things I'm doing behind its back. Rick Sprague

 

[MichaelRed]

I would think that if you're going to go that far, you would just want to make unbound forms. Then it would be no problem to do all of the work inside a transaction.

Actually, using UnBound forms have a REALLY major problem - there is no RecordSource or ControlSource, so the overall concept goes south. It would require setting up (and UGGGGGGGGGGGGhhhhhhhhhHHHHHHHH MAINTENANCE) of the map between controls and fields). This is more-or-less the classic VB process, and I do not feel it is all that much effort, as I have done this many times, but the 'automatic' tracking of recordsource and controlsource to recordset field is certainly convenient.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SUnderwood]

Hmm,

As it happens unbound forms are my preferred method of designing Access databases. Well, strictly speaking I prefer bound forms without bound controls (and no record locking), this method is employed mainly because I don't have VB to work with for either of my clients. With the client that needs the transaction log the database uses bound forms and fields so the code presented would be excellent.

But a transaction log for the bound form-nonbound controls structure would not be unfavourable to the code already presented. Form controls with the same names as the forms recordset fields would contain the New Values whilst form recordset fields would contain the Old Values. Both are easy to examine against one another, and any difference will result in an entry in the transaction log. This would require a few changes to the code (in fact something akin to horizontal transfer) but the structure will stay the same.

(In fact I may use this idea for my first client.)

Sean