Results 1 to 2 of 2
Thread: Can Access do this? (2000)
2004-04-26, 17:51 #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.
2004-04-26, 18:09 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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:
<LI>CreatedBy (text, 50 characters or so)
<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)
<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 Sub[/list]Keeping a complete audit trail would involve creating separate log tables instead of storing information in the records themselves.