Tracking specific user activity using access 2003.

[laserman09]

I have programmed an access database for medical record reasons, and in order not to have a breach in confidentiality, I will need to have a log on the activity of a user when he logs in, ie. be able to see which records/information the user accessed,viewed, and changed. Is it possible to have a tracking system programmed into access that logs the activity of different users?

 

[KenReay]

Hi

Yes

Method depends on how you want to control log in

You could use Access Security, in which case, CurrentUser() returns currently logged in Access User Id, or you can retrieve and use Windows login id, to determine currently logged in Windows user.

Which do you want to do?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[laserman09]

I'm thinking of using access security. How do I configure it to track which records in a dataset were were accessed by a specific user id through a form?

 

[FancyPrairie]

Assuming you have the a field in a table that identifies the user (i.e. strCurrentUser) and you have control on the form bound to that field (i.e. txtCurrentUser).

Then, in the OnCurrent event of the form type the following:

txtCurrentUser = CurrentUser

Everytime a user looks at a record (regardless of whether or not they changed the record), their name will be logged.

However, if you display the form in datasheet view (vs form view) you have a problem. Because now the user could be viewing, say, 20 records at one time and only one record is current. You can loop thru the records shown and set the field txtCurrentUser. But this can become monstrous as the user scrolls up/down 1 or more records at a time.

If you want to know when a record was changed, then in the AfterUpdate event of the form, enter the same thing as above.

 

[psemianonymous]

Access security is crackable, just so you know, so if this is supposed to satisfy HIPAA(sp?) regulations, then be totally sure Access is good enough. It was good enough for Diebold in the voting machines (and I shudder to think).


What you could do, if you need to log every access, is on the form's Form_Current() event, append a record to a 'patient view log' like:

[tt]CurrentDB().Execute "INSERT INTO tblLog (UserName, PatientID, AccessDate) VALUES ('" & CurrentUser() & "'," & Me!ID & ",#" & Now() & "#)"
[/tt]

Something like that.


Oh, and I wrote a FAQ about Access security. It's long but you should probably read it:

Gauging your security needs; alternatives to Access/JET security faq181-3893

 

[MichaelRed]

With some minor embellishments, faq181-291 could also record all accesses to records. You need to be quite careful using Ms. A. in recording activity, as there is no security or event notificatrion of direct access to tables and / or queries, so you need to CAREFULLY disable any / all direct access to these. As far as I know, it takes a (lot) of doing, including (and cretainly not limited to):

[tab]hiding the db Window
[tab]disabling the use of startup bypass functions (keys)
[tab]removal of some menu /tool bar items (properties, Unhide, etc)
[tab]Making sure that all queries are set to run with the necessary permissions.

I'm sure thqat others can (and will) add other considerations and concerns to this list, as it is only meant to interest you in researching the myriad of ways you can get to the data in an Ms. A. db - without having it recorded.



MichaelRed
mlred@verizon.net

 

[FancyPrairie]

foolio12's idea of a separate table is a good idea because you have a history of who has seen the patient's record.

I too have HIPAA to deal with and have been contemplating this problem off and on for awhile. And have yet to come up with a resonable solution to the problem of the users viewing the records in datasheet view. Say, for example, that 20 records at a time are shown in datasheet view. That's 20 updates to the "history" table as soon as they open the form. Now assume that the user scrolls up and then down and then up again, you just logged a bunch of info. Compounding the problem is many users doing the same thing at the same time.

 

[psemianonymous]

The option in your case 'FancyPrarie' is to display only non-private information in the grid, which you don't have to log, and display the specific private information on a patient-by-patient basis. I was wondering how they did this at my last job that used the IDX enterprise medical system. You could do patient lookups by name or date of birth, and I don't think it logged the results of the name lookups--it only logged when you actually 'entered' the patient record and saw more detail than just name/ssn/dob/address.

So in other words, you can work it so that you can still use datagrids but not have to log all those accesses.