Multi-User Access Database - Tracking who makes changes

[sharsy]

Hello,

I have setup an Access Database which about 7 people can enter data
into. We have had a few problems where a 'fairy' has mysteriously been
changing data in the table that they're not supposed to. I was
wondering if it was possible to find out which user is responsible for
making these changes. The database is not password protected or
anything.

Kind regards,

Shari

[Chris O'C via AccessMonster.com]

If you set up user level security (not easy, mind you), you could set it so
that only the db owner can update data in the tables. The rest of the users
will be prevented from making unauthorized changes unless they know how to
circumvent user level security (even advanced level people have trouble with
security).

The mysterious changes will suddenly cease, but you'll have one or more
complainers who "can't do their job!" any more. Have these complainers show
you what they're trying to do, and you'll find the guilty ones. Chances are,
they don't know the results of their actions, so they didn't know they were
making the changes to the wrong records.

Also be prepared to find out the db design makes unintentional changes to
data. For instance, naming a field "Date" is one of the most common mistakes
by new db developers. When a user runs a form, the form can change the date
in a record to today's date. Not the user's fault, but a faulty design could
be this mysterious "fairy" you're trying to nail.

Chris
Microsoft MVP
sharsy wrote:

>Hello,

I have setup an Access Database which about 7 people can enter data
into. We have had a few problems where a 'fairy' has mysteriously been
changing data in the table that they're not supposed to. I was
wondering if it was possible to find out which user is responsible for
making these changes. The database is not password protected or
anything.

Kind regards,

Shari

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200806/1

[Fred Zuckerman]

"sharsy" <sh***@ptpartners.net.auwrote in message
news:19**********************************@p39g2000 prm.googlegroups.com...

Hello,

I have setup an Access Database which about 7 people can enter data
into. We have had a few problems where a 'fairy' has mysteriously been
changing data in the table that they're not supposed to. I was
wondering if it was possible to find out which user is responsible for
making these changes. The database is not password protected or
anything.

Kind regards,

Shari

You didn't mention whether the fairy is making changes via a form or
directly via the tables...

If they're making changes via a form, then it's relatively easy to log
changes using the before update event. You can capture their network login
id and even their computer id.

If, instead, they're making changes directly into the table(s), then it's
difficult, if not impossible. You might have to implement User Level
Security and then remove everyone's permissions to the tables. Then you can
be sure they're making changes via a form and log the changes.

Fred Zuckerman

[Tony Toews [MVP]]

sharsy <sh***@ptpartners.net.auwrote:

>I have setup an Access Database which about 7 people can enter data
into. We have had a few problems where a 'fairy' has mysteriously been
changing data in the table that they're not supposed to. I was
wondering if it was possible to find out which user is responsible for
making these changes.

To add to the other comment with respect to security:

There's a simple example at
ACC2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...;en-us;Q197592

Audit Trail - Log changes at the record level at:
http://allenbrowne.com/AppAudit.html
The article addresses edits, inserts, and deletes for a form and subform.

Modules: Maintain a history of changes
http://www.mvps.org/access/modules/mdl0021.htm
The History Table routine is designed to write history records that track the changes
made to fields in one or more tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[sharsy]

Yeah that sounds like a great idea - they are supposed to be using a
form to enter/change data, but they hardly ever use the form because
they're so used to using the table format. My form is not too shabby
might I add!!

I will try this out.