Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Czech Republic
    Thanked 0 Times in 0 Posts

    Can Access do this? (2000)

    A friene has asked me to help her with an Access problem.

    Her company wishes to merge 3 access databases to make one big access database.

    3 people - at least - will input into this dbase and she wishes to know - as I do - if Access has a facility which will allow those who inputted to the dbase to be identified. I do not think that Access alone can do this.

    I explained that she might get her network people - NT - to look into this but I do not think that the network would show an audit trail of INDIVIDUAL RECORDS that a person might add to the dbase.

    Any help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Can Access do this? (2000)

    Although this is possible in principle, keeping a complete audit trail might cause the database to bloat quickly. You could, however, quite easily keep track of
    <UL><LI>Who created a new record and when.
    <LI>Who last modified a record and when.[/list]This can be done as follows:
    <UL><LI>Add four fields to each table you want to keep track of:
    <UL><LI>CreateDate (date/time)
    <LI>CreatedBy (text, 50 characters or so)
    <LI>ModifiedDate (date/time)
    <LI>ModifiedBy (text, same length)[/list]<LI>Put the following code in a standard module:

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

    Public Function GetUser() As String
    Dim lpBuff As String * 255
    Dim ret As Long, UserName As String
    ret = GetUserName(lpBuff, 255)
    GetUser = Left(lpBuff, InStr(lpBuff, vbNullChar) - 1)
    End Function

    <LI>Create an event procedure for the Before Update event of each form in which new records can be created and/or edited (you must do this in a form, it can't be done at the table level in Access):

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
    Me.CreateDate = Now
    Me.CreatedBy = GetUser
    Me.ModifiedDate = Now
    Me.ModifiedBy = GetUser
    End If
    End Sub[/list]Keeping a complete audit trail would involve creating separate log tables instead of storing information in the records themselves.

Posting Permissions

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