Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Audit trail (2000)

    Hi,
    I have an existing database that I've been asked to do an audit trail on. What is the best way of doing this?
    I'm not able to fiddle around with the permissions due to the restrictions placed on the computers that I'm working on. My thinking would be to have an initial logon form where the user selects their name and password. What I'd then like to do is to track all the work that that user does in the database.
    Is this possible, and what are the implications behind it, if any?

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

    Re: Audit trail (2000)

    It is possible but not necessarily practical to do this. "Audit trail" is a little vague. Exactly what are they wanting to track and to what purpose?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    They wish to track the usage of the database by the individuals (4) who actually use it.
    The database tracks payments in relation to work done on a specific case. The case can have multiple people working for it. Therefore multiple payments.
    Basically each individual who uses it records payments that they have agreed. Currently there is no system in place to record who agreed the payment.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    Not sure if this will help, but works fine in one of my VB applications.
    Create a new module:

    <pre>Option Explicit

    Const MaxLogSize = 2000000
    Private Const MAX_COMPUTERNAME_LENGTH As Long = 31

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

    Private Declare Function WNetGetUserA Lib "mpr.dll" _
    (ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long

    Public Sub WriteLog(sLogEntry As String)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim sLogFile As String, sLogPath As String, iLogSize As Long
    Dim fso, f

    On Error GoTo ErrHandler

    'Set the path and filename of the log
    'sLogPath = App.Path & "" & App.EXEName
    sLogPath = "Choose a path to save the log file" & GetComputerName & " - " & GetNetUser

    sLogFile = sLogPath & ".log"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile(sLogFile, ForAppending, True)

    'Get the size of the log to check if it's getting unwieldly
    iLogSize = GetLogSize(sLogFile)
    If iLogSize > MaxLogSize Then

    'If too big, back it up to to retain some sort of history
    fso.CopyFile sLogFile, (sLogPath & ".old"), True
    Set f = Nothing
    fso.DeleteFile sLogFile
    'And start with a clean log-file
    Set f = fso.OpenTextFile(sLogFile, ForAppending, True)

    End If

    'Append the log-entry to the file together with time and date
    'f.WriteLine Now() & vbTab & sLogEntry
    f.WriteLine sLogEntry & vbTab & Now()

    ErrHandler:
    Exit Sub
    End Sub

    Private Function GetLogSize(filespec As String) As Long
    'Returns the size of a file in bytes. If the file does not
    'exist, it returns -1.

    Dim fso, f
    Set fso = CreateObject("Scripting.FileSystemObject")

    If (fso.FileExists(filespec)) Then
    Set f = fso.GetFile(filespec)
    GetLogSize = f.Size
    Else
    GetLogSize = -1
    End If
    End Function

    Public Function GetComputerName() As String
    Dim dwLen As Long
    Dim strString As String
    'Create a buffer
    dwLen = MAX_COMPUTERNAME_LENGTH + 1
    strString = String(dwLen, "X")
    'Get the computer name
    GetComputerNameA strString, dwLen
    'Get only the actual data
    strString = Left(strString, dwLen)
    'Return the computer name
    GetComputerName = strString
    End Function

    Public Function GetNetUser() As String
    Dim lpUserName As String, lpnLength As Long, lResult As Long
    'Create a buffer
    lpUserName = String(256, Chr$(0))
    'Get the network user
    lResult = WNetGetUserA(vbNullString, lpUserName, 256)
    If lResult = 0 Then
    GetNetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1)
    Else
    GetNetUser = "-unknown-"
    End If
    End Function</pre>


    And use it like this, after an event:

    <pre> WriteLog GetComputerName & " " & GetNetUser & _
    " Your Text " & [Possibly a field name,record or action] & " End Text"</pre>


    Which will probably look something like:

    Computer 1 User6 Edited Record 12354 Today

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

    Re: Audit trail (2000)

    We routinely record who created or last modified a record and when by using Access User Security. You have to provide a text field for the username (readily available with CurrentUser()), and you need a Date/Time field to capture the when info using Now(). There are a number of threads about doing this sort of thing - but it needs to be done from a form in Access - SQL Server gives you the option to do it at the table level using triggers.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    Thinking on my feet a little here.
    In the payment table I have fields that detail who the payment is too (Advocate), an estimated figure(Estimate), an actual figure(Actual) and the date payment is sent (DateSent). Plus some others.
    Could I add a field Employee, with the names of the 5 people who authorise payments into the table. From here, on the after update procedure of the field Estimate (which is the one I'm interested in), set the value in Employee to Null and request (via MsgBox) that the user fill in the value from a combobox (CboEmployee) dependant upon whether the value in DateSent is today and Estimate is not Null. Ideally a preset password would also be used, but not essential.
    This way I could record who made what payment.
    I'm no good with coding by the way!!! <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

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

    Re: Audit trail (2000)

    Sorry for the long delay in responding - I've been travelling a fair bit. You could implement a solution the way you suggest, but it will not be very robust, and will require writing a significant amount of code. It does take a bit of reading to understand it, but Access User Security will go a long ways toward solving your problem, and will require only a small amount of code at the form level. See the thread on a similar topic that contains <post#=408378>post 408378</post#> for some of the details. You might also want to take a look at our User Security Tutorial.
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    Hi,
    Thanks for the reply. I have been pondering on Access Security for a while and wondering whether I should broach the subject at some point.
    I've had a look at this tutorial and have an understanding (although limited) of the concept of Access Security.
    As I understand it , I need to make workgroups, for instance, Admin, Users & Developers. What I'm not too sure on is how to create a workgroup. Currently (and annoyingly) the databases that I work on are designed on a networked system running through XP Pro. There have been limitations placed so that access to the system files is not possible. Does this mean that I cannot implement security through the jet engine? Or have I misunderstood?
    Once I understand this bit, I think the rest will fall into place.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    You create a workgroup file (file with extension mdw) by using the WRKGADM.EXE program that is locate for a default Office installation in the c:Program FilesMicrosoft OfficeOfficeLanguage Number directory (Mine is 1033). You only have to have access to this file to implement security.
    You create only one workgroup file for one application. You can use the same workgroup file for different applications, but I think this is a bad practice. Once you have create your workgroup file you can create users and groups and assign the users to the groups and then assign permissions to the groups.
    You can download another useful article here : ACC: Microsoft Access Security FAQ Available in Download Center

    As you begin with user security, implement it on a copy of you database and keep the unsecured copy in case you have to start over.
    Francois

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Audit trail (2000)

    Ah ha, this is the problem. On the computers at work I do not have access to any of the actual programs. Like the fools we are, we can only watch from the sidelines and not play with anything!
    Presumably this means that I will not be able to implement any security as I cannot access the WRKGADM.EXE directly.

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

    Re: Audit trail (2000)

    Wrkgadm.exe is installed with every instance of Access, and you can actually use an existing workgroup file, although it will not be truly secure since it was created by Admin. Another option is to use the Security Wizard which will do the job for you. If your location is running applications across the network, you do truly have a serious problem in doing anything from a development perspective, and beyond that, performance is likely abysmal.
    Wendell

Posting Permissions

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