Locking Records/Audit Trail

[scott04]

I have a database that is used by multiple locations (total of 3). The
database is still in the design phase and not completed yet. One of the
locations would like the databased design so that their records cannot be
edited by another location. I know that you can set the Allow Updates
property of the form to No and then have some sort of command button allowing
you to enable it something needs to be edited.

My main questions is how would I go able to create some sort of audit trail
that gives me who made the changes, what the changes were, and the time
stamp? I have never needed to design an audit trail for any of my designed
Dbs and any help or thoughts on this situation is appreciated!

 

[mscertified]

You can easily capture the userid of the logged on user and save this and a
timestamp in updated records. Or, you could save details of all updates in a
log table.
You could keep each locations records in a separate table and give each a
front-end with only their tables linked in.
Or, you could combine the records in one table and then separate the data
when it is accessed.
It all depends on what you want and what the needs are. Do other locations
even need to view other locations records?

-Dorian

 

[scott04]

Dorin,
Thanks for the info. There are three locations that need the database. To
help you better understand lets call them location 1, 2, and 3. Location 1
and 2 will be the primary add and update records users. While 3 will be more
of an admin role but will change information from 1 or 2 if necessary(more of
an auditor location). Location 1 does not want Location 2 to be able to edit
their records unless there is an audit trail which shows who changed what. I
like your idea of saving the details in a log table but how would you setup
this table? I have an idea but i have never setup an audit or log table.
Any thoughts? Thanks again