Keeping a copy of the old record being edited

[ChrisJF]

Hi there, there may be a simple answer out there, but I want to keep an original copy of the record being edited/changed. I believe that any code would have to be done against the beforeupdate event of the form involved.

I need this for audit trail purposes.

Is it possible by using a macro with a query or do I need a bit of code?

Regards
Chris

 

[DrSimon]

I'm not aware of any simple answer to this. I haven't time to give an real detail, and in my opinion if you have an important database any advice that you need, ought to be on a professional basis. (No - I'm not touting for business - that's not my field!)

However before you go down that path there are a number of major points to consider:
1. The database will need to be secured with passwords to prevent anyone getting at the audit data and amending the 'truth'.
2. How much audit data are you going to collect? If you have an active database with many entries being made, it is easy for the audit files to rapidly get larger than the actual data files. You will need some way of purging the audit data.
3. What do you need to audit? Every single data change? Date/Time of each change? Who made changes? Will you need to audit enquiries as well as changes?
4. Remember there are 3 main types of data changes: adding, amending and deleting.
5. I suspect that this won't be a problem for you, but a major concern can be transaction processing. Just because a user thinks they've updated a record does not actually mean that database update has actually happened.
6. Finally who is the audit aimed at? Is it just "we'd like to know" or is it for legal/accounting purposes? Clearly if if it's for the latter you need to made sure that all the rules are obeyed.
Hope this helps a bit
Simon Rouse

 

[tonyireland]

This might be worth looking at

http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

 

[MichaelRed]

There are seperate 'schools' of thought on audit records. An alternative to saving hte entire record prior to each change is to save only the changes. In instances where there is considerably more edit activity than add / delete activity, this is somewhat less 'expensive' (in storage at least) than simply copying the record (along with tthe who-done-it info). See faq181-261 for an example of the alternative.

Otherwise, pay heed to points made by DrSimon. ?they are valid considerations. In particular, hte 'audit' table growth is (or at least CAN be) awesome and left unchecked will rapidly impact the performance of the primary app (and not in a good way). I generally prefer to copy aduit records of inactive records to a seperate historical / archive table than their actual (total) deletion. In the apps where I have implemented this, there was a clear indication that a (primary table) record was inactive, so I could easily copy it to the archive db, delete it from the 'primary' record table and then do hte same with the 'audit' records. Even with this, it was necessary to create new 'archive' databases periodically.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ChrisJF]

Thanks for the comments, I have given all the above some serious thought. I believe the best way to go is to have an archive table which is appended with the original record pre update.
My database uses a query to output results in a form.
Whenever a record in that form is edited, I want to append this record to the archive table.

I'm not sure how to do this! If I try to use a QBE append query, I get the full search results appended to the table and not just the active record which is being edited.
Help please!