[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1736
  • Last Modified:

Audit Trail in Access

What are some of the better ways to establish a simple audit log of what users change while editing data in an access table?  Let's assume they will be doing so not directly in the tables but through a simple form which has your basic textbox data linked fields.

They will change something (anything0 and then either click the pencil or the save button and close the form.

I'd like this to somehow create an entry in a history table of this user it was and what they changed, so we could pull up a change history on any given record from that particular table.

Thank you in advance for your thoughts and knowledge sharing.
0
Informative
Asked:
Informative
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In any table I have data entered by users I typically have four extra columns:  dt_added, dt_last_updated, txt_added_by, and txt_last_updated_by, and store in them the respective added date/time and person, and whenever it's updated the update date/time and person.

These fields are never editable by users, and are edited programmatically in form's .BeforeInsert or .BeforeUpdate events.

If you want to know what field changed, it would probably be better to save the record to a History table like you indicated.

Hope this helps.
-Jim
0
 
Steve BinkCommented:
I'll go with Jim's response.  Access does not provide for field-level auditing in ANY sense of the concept, and trying to build a system for it will be time-consuming at best, homicidally frustrating at worst.  The best system I've seen goes something like this:

1) User opens a form to a specific record.  Access records that action in the audit table.
2) As the user changes field data, the form is populating a manually constructed recordset with each change.  The recordset includes fields for table name, field name, record id, old value, new value, timestamp, and user id.
3) If the user saves the record, the manual recordset is then appended to the audit table.  Otherwise, the audit table receives a record indicating the change was cancelled, and the manual recordset is discarded.

It was intensive to build, and required event code for EVERY CONTROL on every audited form...quite the project.  Unless this is going to be a permanent ongoing project, I would not even bother with it.  Even if it is a permanent ongoing project, I would still recommend switching to a db platform with the functionality to support what you need, like MSSQL.
0
 
InformativeAuthor Commented:
<<I would still recommend switching to a db platform with the functionality to support what you need, like MSSQL.>>

OK then assuming we go the way you suggest and use Access as the front end and SQL back end what is your best suggestion for how to do this with that particular configuration?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Steve BinkCommented:
Triggers!  MSSQL has that neat little functionality to support exactly what you're talking about.  A trigger is kind of like an event for an Access form, but it works on table changes instead.  You can identify triggers for all types of events in MSSQL, including changing data in a record.  Here's an excerpt from one of the "Overview" pages from the MSSQL Books On Line.  If you would like to download this, you can find it at:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

---------------------------------------------------------------------------------------------
Microsoft® SQL Server™ 2000 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. A trigger is invoked in response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

Triggers are useful in these ways:

Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.


Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.
Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.


Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.
---------------------------------------------------------------------------------------------
0
 
Steve BinkCommented:
>>> Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.

I just wanted to specifically point out that particular sentence...
0
 
InformativeAuthor Commented:
OK let me try this.  It seems I recall in the upsizing wizard it allows for uploading the table relationships to the SQL2000 server but these require unique indexes.  I'm thinking more like this first suggestion that whenever the user save or inserts a record may be the best place to simply copy the currently edited record plus a time and date stamp field.

The SQL upsize wizard actually asks about adding a datetime stamp field automatically.  I'm guessing this may be laying the groundwork for just such audit capability.

Thank you for the excellent information.  May not get back to award points this afternoon - too much sunshine outside to do this all afternoon but will be sure to award points by Monday worst case.
0
 
Steve BinkCommented:
Yeah, upsizing can take you through some aggravation, but it can be well worth it if you need that advanced functionality.  You can be sure the wizard will do as good of a job as possible in transfering all your information, but there are certain things you'll still need to go back and 'clean-up', so to speak, relationships and keys being first in that list.

The only problem with the history table is database bloat, which may or may not be a concern for you.  Keep in mind that the maximum db size is roughly 2gb in Access...any larger and <poof>
0
 
InformativeAuthor Commented:
Agreed.  All the more reason to upsize and run it on SQL plus SQL has the nice job scheduler agent which can make clearing old history to avoid the bloat something completely unattended.  No compacting.  No repairing.
0
 
ki_kiCommented:
Hi all, I have been following this intersting discussion. I just need to understand one thing. Are you guys saying that Informative, keeps using Access as front end and SQL server as back end? Thx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The issues of SQL Server vs. Access and tracking history are not directly related, it's just that any process which will likely generate a lot of records, like tracking history will, adds to the demands of the back-end database, which is the domain of Access upsinging to SQL.

There are many reasons to upsize to SQL, such as a need for greater security, 24x7 use, number of concurrent users/transactions, speed in executing large chunks of queries/code, etc.

0
 
Steve BinkCommented:
ki_ki: Yes, I am recommending the use of MSSQL as the back-end, and Access as the front-end.  Well, Access, or ASP, or a VB project.  It does not really matter, since this is just the doorway to the data.

As jim brought up, the main reason to upsize in this particular scenario is the auditing process.  First, it will generate TONS of records...at least one audit for every 'actual' record in the database, and additional records for every audited change.  On a database with records that change fairly often, you could reach the 2g size limit rather quickly in Access.  Two gigs is nothing to MSSQL.

Second, MSSQL has functionality that supports auditing and makes the programmer's job all that much easier.  Trying to do this in Access would be painful at best, since Access provides for no events to support it.  MSSQL has triggers and constraints, which can both use the full functionality of SPs.
0
 
ki_kiCommented:
Hi rouinet, i have a VB6 question and tried to email you but it didn't work!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You may want to take a second look at Routinet's email address, and apply some logic.  ;)
0
 
ki_kiCommented:
i know.....i was stupid!
0
 
Steve BinkCommented:
Well, you can always ask a question, then post a link to it somewhere you know I'm watching.  :)
0
 
InformativeAuthor Commented:
OK this works pretty well.  

Added user and date-time fields (hidden) to the form being edited as well as the table being edited (two new fields there)  Placed the code to slap the users ID and now() into those two new fields in the beforeupdate beforeinsert events.  Then dropped the code to replicate the current record into a history table in the afterupdate afterinsert.  Upsize the DB to MSSQL and create a simple Agent scheduled job to purge old records from history weekly keeping only the last couple of months of history to limit it's size.

Thanks for all the great input.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now