Audit Trail for Access

[Greg Smith]

We have set up an MDW file with 2 users - READONLY and
UPDATEONLY. Some users can only read data while others can
update data in the datasheet view.

We want to enhance the application so that audit trail is
added. I would like to ask
1) Is it necessary for users to update / enquiry via a
FORM instead of going to datasheet view directly for
adding / updating /deleting entries ?
2) Does the Audit Trail uses their login name - ie
READONLY and UPDATEONLY ? If YES, it seems that it is
desirable to create user name for each user (For Audit
Purpose).

Thanks

 

[MacDermott]

1. If you want to record changes into an Audit Trail, you'll need to write
some code to collect and save this information.
Since Tables and Queries don't have any events which could trigger that
code to run, you'll have to use a Form for this purpose. But the form can
be quite rudimentary if you wish.

2. You CAN use the CurrentUser() function to return the name of the current
Access user. There are also many other ways to gather an identifier for the
user making the change. Since you're already collecting a UserName on
login, though, it makes sense to use that.
A suggestion: Create a READONLY and an UPDATEONLY Group, and assign
privileges to each group. Then when you add/remove users, you'll simply add
them to the appropriate group, instead of having to set all the permissions
for each user.

HTH
- Turtle

 

[Juan Paulo]

If preferible to add the workgroup security for the
database and set up Users, Groups and permissions to the
groups (recomendable).

This sets the privileges of the users to the objects
(tables, querys, forms, reports, modules, etc), but not
track the activities of each one.

If you want to track some (or all) the activities of the
users, define a table to keep the log of the use of the
objects. And develop some functions to save this records.

But never permit that the user interact with the DB
window, control al the system via a Control Panel or
Switch board (see tools, setup, disable DB window or
via code).

Saludos
Juan paulo

 

[Albert D. Kallal]

Yes, you should give each user a logon. However, you can also log the
computer logon name, and also the workstation name also (I often log all
three).

And yes, you kind of have to do this through the forms interface.

Here is a post of what I did for one of my applications:

http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=8341&
ix