Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Audit Report (2002/SP2)

    I have about 12 users in my database and we use a backend/front end database. Each location is able to access the front end only. I would like to know if there is a report or query I could make that would let me know of any changes, additions or deletions to the database. We have records disappearing and naturally, no one is doing it.

    Thanks!
    Wendy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Audit Report (2002/SP2)

    In Access, you can only do this through a form. If users edit data directly in a (linked) table or query, you have no way of knowing who did what. In a form, you can write code in the After Update, After Insert and On Delete events of the form. You can log the name of the Access user or network user, as well as the type of action and the date/time in a separate table.

    If you really want to keep track of changes at the table level, you would need to move the data to SQL Server.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Burbank, Illinois, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Audit Report (2002/SP2)

    Hi Hans

    All the changes are made through forms since no one is allowed into the tables. Can you give me a hint on how to do this?

    Thanks
    Wendy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Audit Report (2002/SP2)

    You could create a table tblChanges with the following fields:

    ID: AutoNumber (primary key)
    ObjectName: Text (size large enough to accomodate the longest table name)
    Action: Text (size 1, for example M = modify, A = add, D = delete)
    UserName: Text (size large enough to accomodate the longest user name)
    DateTime: Date/Time, default value Now()

    If your have user level security, and users have to log in to the database, you can set the UserName field to CurrentUser; if you don't have user level security, this will always be Admin, however. In that case, you can retrieve the network user name using the code provided below.

    Example code for After Update event:

    Private Sub Form_AfterUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblChanges", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!ObjectName = Me.Name
    rst!Action = "M"
    rst!UserName = CurrentUser
    rst.Update

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    And analogously for After Insert and On Delete, with "A" and "D" instead of "M", respectively. If you want to use the network user name instead of the Access user, put the following code in a general module, and use NetUser instead of CurrentUser.

    Private Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _
    (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

    Public Function NetUser() As String
    Dim strName As String, strUserName As String
    strName = vbNullString
    strUserName = Space(255)
    If WNetGetUser(strName, strUserName, Len(strUserName)) = 0 Then
    NetUser = Left(strUserName, InStr(strUserName, vbNullChar) - 1)
    Else
    NetUser = "-unknown-"
    End If
    End Function

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Audit Report (2002/SP2)

    Hi Hans, i've just been starting to implement this kind of audit tracking as you've outlined. It works well in my forms, but how can i tell the exact record which has been modified for example? The tblChanges is giving me the user names of the people who've modified, added or deleted records in the table, but not which record. Can this be done? thanks, Van

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Audit Report (2002/SP2)

    If all your tables have a numeric unique idenifier, for example an AutoNumber field, you could add a field ID (number, long integer) to the tblChanges table. In the code for the After Update, After Insert and On Delete events, add a line

    rst!ID = Me.[UniqueKey]

    where UniqueKey is the name of the unique identifier field for the record source of that form.

  7. #7
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Audit Report (2002/SP2)

    Thanks Hans, that work great. cheers, Van

Posting Permissions

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