Identify original user ID (Access 97)

Topic

New Reply

Is there a function to identify the original user ID of the person who entered a record? I want to be able to have multiple updates to the records (e.g. managerial approval) and want to trace back to the original person who entered it.

thanks
christine

Reply | Quote

Replies

… don’t know how you could track this without saving the users name as a separate field in the table that the data is entered.

Hopefully, for your sake, I am incorrect.

Reply | Quote

We do this pretty regularly – the first step is to have user security activated, as otherwise you get Admin for all records. We normally use two fields to track who created the record and when, the first a text field of 20 characters or so, the second a Date/Time. The default values can be placed in the table for this kind of thing.

The more complicated thing is to track changes. We do it with triggers in SQL Server or the MSDE/Desktop Engine, but if you are working with an .mdb you don’t have that option. In that case you can capture who changed things, and actually keep an archive if you wish, but it requires extensive use of VBA behind the form. The problem is that doesn’t work when someone makes changes at the table level. If you lock things down to the point where users can’t normally get to the tables, then the smart form approach may be OK.

Reply | Quote

This sounds like a great option. Even if I can’t track the change due to the complexity, I can gather as much information as possible. When you said to create 2 fields: record creator and date/time of creation, what is the name of the fuction for these 2? At least this way, I can generate a report for the managers to follow up on abuse of the system.

Reply | Quote

These are standard functions for Access. The field tracking who created a record can have its default value set to CurrentUser, and the date tracking when the record was created should be set to Now. Of course this isn’t foolproof, as users could login with someone else’s UserID if no password is assigned, or if they know the other person’s password, and the date/time can be off since it comes from the local workstation where the clock may not be correct, but it’s usually accurate enough to be of value.

Reply | Quote

A related question here as I have been following this thread. Would it be possible to identify the computer id where the data is input from?

Reply | Quote

Recommend check out this MSKB article – provides WIN API for getting computer name, among other useful workstation related functions:

ACC2000: How to Retrieve Workgroup Information Under Win32

In addition to WIN API, you can use the Windows Scripting Host (WSH) to get computer name, using the WshNetwork class ComputerName property. Example (WIN XP, object names may differ in earlier versions of Windows):

Public Function GetComputerName() As String

Dim wsh As New IWshRuntimeLibrary.WshNetwork
GetComputerName = wsh.ComputerName
Set wsh = Nothing

End Function

To be able to use above example, set reference to Windows Script Host Object Model library (wshom.ocx)

HTH

Reply | Quote

Thanks Mark. I will give this a try.

Reply | Quote

As Wendell suggests, it’s nearly impossible to do this without using Access security. Even if you use the workstation login, which can be retrieved with an API call, there is no guarantee that the person entering the data is the person who logged in to the workstation.

Reply | Quote

Sorry, I assumed the implication of already having Access security. Based on other threads that both you and Wendell have responded on, I have setup security with 3 group levels (admin, mgr, user) and about 30 users. I have that up and running successfully, (THANK YOU!!!!!) and what I want to do now is identify security breaches (in somewhat of a fashion). I want to identify the original user that setup the record, the name the user applied to the record, and the manager’s ID that approved the record. I want to make sure that users are not entering data under someone else’s name.

Reply | Quote

I don’t really see how you could do that given the propensity of users to “hide” their logins and passwords on post-it notes attached to their monitor. The only thing you can determine is the login, not whether that is really the person doing the data entry.

Reply | Quote