Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Norwich UK
    Posts
    20

    Post Unanswered: I need to Create a User History Log

    I've created a Productivity Database (Access 2000) which can be used by anyone but I'd like to Log, in a table, who opens it and when, using they're system username eg jbloggs.

    I've tried in vane for many hours without success... HELPPPPP!!!!

  2. #2
    Join Date
    Jul 2003
    Posts
    26
    I found this link in a previous thread, might help.

    http://www.mvps.org/access/api/api0008.htm

    Allows you to get the network user name.
    Life's a bowl of punch, go ahead and spike it.

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I would make an auditTrail table.
    Fields like,
    AuditID
    User
    Time
    Action
    DBVersion
    Comments

    Make a common function for placing entries in the audit trail. The parameters would be the users name, the action, comments, etc.

    When the main form opens or loads, place an entry in the auditTrail table.
    When the user closes the form, or unloads the form (somtimes they click the X on Access and don't close your form nicely), call the function again.

    Filling the elements...
    1) DawG's post will help get user name.
    2) Now() will give Date & Time stamp
    3) The action gets passed in from the function call (e.g. "LogIn" or "LogOut")
    4) I would make a simple function that returns the version number of the database.
    5) Add comments for additional audit trail calls.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  4. #4
    Join Date
    Jan 2004
    Location
    Norwich UK
    Posts
    20
    How exactly could I write the "user" and "time" into a table?

    I'd like all this to be done against an "onLoad" via the main Form opening.. if that's possible.

    I've got the GetUserName and Date bit sorted and it works fine, just don't know how to get it written into a table etc.

  5. #5
    Join Date
    Jan 2004
    Location
    Norwich UK
    Posts
    20
    Not to worry... I've susse it!!

    Thanks for you help

  6. #6
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18

    Audit trail

    What was your final outcome ? Did it work? I am trying to get a history table of record changes for an unbound form.

  7. #7
    Join Date
    Mar 2004
    Posts
    82
    Hi,
    I found a very simple solution in a book I recently purchased - Access Cookbook by Ken Getz, et al (O'Reilly). See page 247 under "Managing Data". I hope this is not considered advtg; only trying to be helpful.
    Jabo

  8. #8
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18
    I don't have the book. Can you assist me in the code ? What am I doing wrong ?

  9. #9
    Join Date
    Mar 2004
    Posts
    82
    Originally posted by CKENTEBE
    I don't have the book. Can you assist me in the code ? What am I doing wrong ?
    Ok, here it is with ack to the copyright holders:

    In every one of your tables, add the following four fields:
    DateCreated (field type: Date/Time) Default value = Now()
    UserCreated (text)
    DateModified (Date/Time) Default value = Now()
    UserModified (text)

    Add textbox controls in your data entry forms for the above four fields (delete the label fields that come along with them).

    In the property for each control, set Enabled=No; Locked=Yes; Tabstop=No. You may wish to set the controls to Visible=No so that they don't show up on the form.

    In the property for the form, add the following event procedure to the BeforeInsert event:

    Private sub form_beforeInsert(Cancel as integer)
    Me!UserCreated = CurrentUser()
    End sub

    In the BeforeUpdate event on the form, create the following:
    Private sub form_beforeupdate(cancel as integer)
    Me!DateModified=Now(0
    Me!UserModified=CurrentUser()
    End sub

    This would record the name of the person/user and the date/time whenever a record in a specific table was newly created or modified.

    However, the CurrentUser() function will not work in a network environment, for which you will have to use the following API calls (and maybe tweak them a little):

    Create basNetowrkID module with this code:
    Option Compare Database

    Private Declare Function acbGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Private Declare Function acbGetComputerName _
    Lib "kernel32" Alias "GetComputerNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long

    Private Const acbcMaxComputerName = 15

    Function acbNetworkUserName() As String
    ' Returns the Windows 2000/XP network login name @ computer.
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = acbGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
    acbNetworkUserName = Left$(strUserName, lngLen - 1) & "@" & acbComputerName()
    Else
    acbNetworkUserName = "" & "@" & acbComputerName()
    End If
    End Function

    Public Function acbComputerName() As String
    ' Retrieve the name of the computer.
    Dim strBuffer As String
    Dim lngLen As Long

    strBuffer = Space(acbcMaxComputerName + 1)
    lngLen = Len(strBuffer)
    If CBool(acbGetComputerName(strBuffer, lngLen)) Then
    acbComputerName = Left$(strBuffer, lngLen)
    Else
    acbComputerName = ""
    End If
    End Function

    Good luck!

    Jabo

  10. #10
    Join Date
    Feb 2003
    Location
    Toronto, Canada
    Posts
    18

    Red face

    Thanks but what I have done has all but the changes. I want to have a record of the old values and the new values in the table.

  11. #11
    Join Date
    Mar 2004
    Posts
    82
    In that case, this wouldn't work. It only keeps a record of who did what when. For a copy of the old record you will need to write lengthy code. Access was not meant to do that (it's the province of ther server-based enterprise databases).

Posting Permissions

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