Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Identify original user ID (Access 97)

    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
    thanks
    christine

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Identify original user ID (Access 97)

    ... 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.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Identify original user ID (Access 97)

    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.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Identify original user ID (Access 97)

    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.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Identify original user ID (Access 97)

    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.
    thanks
    christine

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Identify original user ID (Access 97)

    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. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> The only thing you can determine is the login, not whether that is really the person doing the data entry.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Identify original user ID (Access 97)

    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.
    thanks
    christine

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Identify original user ID (Access 97)

    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.
    Wendell

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Identify original user ID (Access 97)

    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?
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Identify original user ID (Access 97)

    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

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Identify original user ID (Access 97)

    Thanks Mark. I will give this a try.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •