Create Audit Log when changes are made in form

[saraUSIT]

I am trying to create an audit log - which will add a record to an audit table-whenever a record is added, deleted or changed. I am using an access form that is in datasheet view-and the user has the ability to delete the record just by selecting the row. (To the user it looks like he is directly in the table) He will add a new row by clicking on the new record button on the bottom of the datasheet form.

I need this audit table to know when a record was deleted and who did it- I know how to track the date and user name - I am just not sure how to bring over the data from the original table before it is deleted.

Thanks

 

[lameid]

I believe there is a FAQ on this in one of the Access forums but in a nutshell you have to use form events to capture the data... For example for changes, you need Before Update. There are other events to capure other data actions that can be used to log changes. The problem with this is that it only happens when the form is used to do the data entry. If someone directly touches the table, you won't have a log of it. I'm still using Access 2003... in that you could use Access security to ensure short of hacking that the form is used.

Another solution is to use SQL Server (Express) for your backend data and write triggers to do the logging. This is of course a platform shift and not to be taken lightly. The only real restriction is that you can only connect 5 users to the same database at a time with the express version. Aside from the hassle of moving the data and learning the right way to hit SQL from Access for performance reasons, that is the only issue and only possible deal breaker to using SQL Express.... On the other hand you might have just as many problems with locking etc. with more than 5 users in Access.