detect user

Irene · May 26, 2008

i have a database is split into two mdb files, one is in workstation, and the
other one is in the data mdb (server file.) Can Microsoft Access detect who
was edit the data for the database? i want to detect who, when and what the
user have been edit or add in the database. can anybody help me?

Daniel Pineault · May 26, 2008

Irene,

There are ways of traking this, but you basically end up having a database
to track your database! You really need to ask yourself if it is worth
doing. If you implemenet security to ensure only the appropriate users can
make changes..., do you really need to go down the path of logging every
action that occurs?

But the short answer is yes it can be done. Using commands such as
currentuser() you can identify the user that made a change. In conjunction
with your form events (after_update, etc.) you can create a generic function
to write the username, date/time, form name, .... into an audit table of
sorts.

Take a look at http://allenbrowne.com/AppAudit.html for a good starting
point. From there you can customize it to suit your needs.

Personnally, I have never needed to go down to that level of user tracking.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

Irene · May 27, 2008

hi Daniel,

thanks for your reply. i need to do like that because some of our staff they
are didn't admit when they are do wrong. so that i force to do like this to
prove it. when something data going wrong, they will blame is system problem
and not admit they key wrongly or others reason. in your opinion, am i worth
to do it?

i have try the code link u give me, but have some problem when running. in
the before update event, Call LogError there got bit problem. how i go to fix
it? cos i'm not so familiar with the coding. hope that u can teach n help me.
thanks!

Daniel Pineault · May 27, 2008

Irene,

I am more than willing to help you get Allen's Audit code working, but you
are going to have to give me more information about what is wrong. Do you
get any errors? What number? What is the description of the error? Give me
some details to work with.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

Irene · May 27, 2008

hi Daniel,

i'm follow the instruction in the link u give it to me. i have paste the
code in the module. after go to debug, and it was come out a msg box, compile
error: sub or function not defined. then i click ok, it was highlight Call
LogError. it is appear after the Err_Handler and before the Resume
Exit_Handler. i don't know what is the number for that line.

roccogrand · May 27, 2008

Irene,

There is a technique in the Access Cookbook by Ken Getz, P. Litwin and A
Baron that may help you.

It calls for adding four fields to your audit table: DateCreated;
UserCreated; DateModified; and, UserModified. The date fields are set to
Now().

You then add these fields to your forms. They can invisible if you need to
audit usage candestinely.

You then set the Enabled property of the controls to No and Locked
property to Yes. Next you create a BeforeInsert event with one line:
Me!UserCreated = CurrentUser().

Then create a BeforeUpdate event with the first statement as Me!DateModified
= Now(). Follow that line with Me!UserModified = CurrentUser(). The
CurrentUser function needs to have user-level security implemented (this
explains why it didn't work for me this week in Access 2007).

The Cookbook has some very useful explanations for coding Access. I am
waiting with checkbook in hand for the Access 2007 edition.

HTH.

LDN

Jan Baird · May 27, 2008