Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old June 14th, 2005, 10:04 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Creating audit trail of all edits to database

Can anyone help with creating audit trail of all edits made to a table/form, (date, time of the initial loading of the table or form and all subsequent changes, plus identification of who entered the data and who subsequently made changes and document reason why changes were made.

Data can be entered in a form. If changes are made to the form, a record is kept of the original entry and the details are stored in another table.

Reply With Quote
  #2  
Old June 14th, 2005, 02:26 PM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 4
Create a table "Audit" and add the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser
Reason

Behind the forms module, copy and paste the following code:

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

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

In the BeforeUpdate for each of the controls you want to audit on your form add the function TrackChanges

This will record in the Audit table the name of the Form, the specific Control, the date and time it was changed, the old value and the new value for the control, the current user making the change and the reason for the change.

It isn't as complete as what you wanted but it is what I use. I didn't find it necessary to record when the table was created or who created the table originally. I was only interested if the data was changed after the record was entered.

Hopefully this is something you can use.

lwells

Reply With Quote
  #3  
Old June 15th, 2005, 10:09 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Audit Trail

Thank you for the code to create an audit trail. I had to do some tweaking as I had some error messages "Compile Error - Use-defined type not defined". I added in DAO to the Track Changes and changed the Me to Screen as the error message read "invalid use of ME keyword".

All fields to the Audit table are being updated, but the only problem I have left is the form name is not being added to the Audit table. Can you help me any further.

Many thanks.



Option Compare Database
Option Explicit

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

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

Reply With Quote
  #4  
Old June 15th, 2005, 11:43 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 4
The function TrackChanges() needs to be in the forms module not a global module in order for the Me.Name to work. So try this:

Screen.ActiveForm.Name

See if that will correct your problem.

lwells

Reply With Quote
  #5  
Old June 16th, 2005, 08:25 AM
Csteed Csteed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2005
Posts: 9 Csteed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 35 m 13 sec
Reputation Power: 0
Thank you this worked!

If I want to add additional data from the form to the audit table i.e. control "RefId", how would the code be inserted into module?

Reply With Quote
  #6  
Old June 17th, 2005, 08:01 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 4
I assume you are wanting to identify which record was changed which is the Refld in your form. Add the following field to your Audit table - RecordID Next add this additional snippet to the existing code.

rs!RecordID = Screen.ActiveForm.CurrentRecord

This will add the current record to the Audit table to identify which record was changed.

lwells

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Creating audit trail of all edits to database


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway