Hi all, I have a Data Audit Trial code, which is intended to track Deletes, Edits, and Inserts. But for some reason I can not get it to work. Maybe someone can check my work to point out my mistake. Thanks.
Details are as follows:
the name of the table to be audited: Data Entry
the name of temp table: audTmpDataEntry
name of audit log table: audDataEntry
ID the primary key of the table being audited
I made the tmp and audit log table as follows:
In the Database window, Copied (Ctrl+C) and pasted (Ctrl+V) the table to be audited. Chose the "Structure Only" option so the data is not copied, named audTmpDataEntry
Opened the temp table in Design view. Removed all indexes, including the Primary Key. Changed the AutoNumber field type to a Number, Long Integer. Removed any Validation rules from the table and all its fields. (Note: the temp table has no primary key.)
Still in Design view of the temp table, inserted these three new fields at the top of the table (i.e. before other fields):
Field Name Field Type Size
audType Text 8
audDate Date/Time
audUser Text 20
Copied (Ctrl+C) and pasted (Ctrl+V) this table to the true audit log table, named audDataEntry.
Opened the audit table in design view. Inserted a new AutoNumber field, and marked it as Primary Key, named audID.
The Audit module trial is as follows:
' Purpose: Audit trail, to track Deletes, Edits, and Inserts.
' Does not audit any Cascading Updates/Deletes.
' Requirements: The table to be audited must have an AutoNumber primary key.
' You need a temp table containing the same fields as the table to be audited, plus:
' Field Name Type Size To Contain
' ---------- ---- ---- ----------
' audType Text 8 Delete, Insert, EditFrom or EditTo.
' audDate Date/Time Date and time of operation.
' audUser Text 20 =CurrentUser()
' In addition, you need an audit logging table with all these fields and an
' AutoNumber primary key.
' Result: The audit table will contain one record for each deletion or
' insertion, and two records for each edit (before and after).
' Delete Copy of the deleted record, marked "Delete".
' Insert Copy of the new record, marked "Insert".
' Change: Copy of the record before change, marked "EditFrom".
' Copy of the record after change, marked "EditTo".
' This approach, together with the sequential numbering of the
' AutoNumber in the audit table makes tampering with the audit
' log more detectable.
Option Compare Database
Option Explicit
Function AuditDelBegin(sTable As String, sAudTmpTable As String, _
sKeyField As String, lngKeyValue As Long) As Boolean
On Error GoTo Err_AuditDelBegin
'Purpose: Write a copy of the record to a tmp audit table.
' Copy to be written to real audit table in AfterDelConfirm.
'Arguments: sTable = name of table to be audited.
' sAudTmpTable = the name of the temp audit table.
' sKeyField = name of AutoNumber field in table.
' lngKeyValue = number in the AutoNumber field.
'Return: True if successful.
'Usage: Call from a form's Delete event. Example:
' Call AuditDelBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID)
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
Dim db As Database ' CurrentDb()
Dim sSQL As String ' Append query.
' Append record to the temp audit table.
Set db = CurrentDb
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & "
;"
db.Execute sSQL, dbFailOnError
Exit_AuditDelBegin:
Set db = Nothing
Exit Function
Err_AuditDelBegin:
Resume Exit_AuditDelBegin
End Function
Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
On Error GoTo Err_AuditDelEnd
'Purpose: If the deletion was completed, copy the data from the
' temp table to the autit table. Empty temp table.
'Arguments: sAudTmpTable = name of temp audit table
' sAudTable = name of audit table
' Status = Status from the form's AfterDelConfirm event.
'Return: True if successful.
'Usage: Call from form's AfterDelConfirm event. Example:
' Call AuditDelEnd("audTmpDataEntry", "audDataEntry", Status)
Dim db As Database ' CurrentDb()
Dim sSQL As String ' Append query.
' If the Delete proceeded, copy the record(s) from temp table to delete table.
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
Set db = CurrentDb()
If Status = acDeleteOK Then
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
db.Execute sSQL, dbFailOnError
End If
'Remove the temp record(s).
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
AuditDelEnd = True
Exit_AuditDelEnd:
Set db = Nothing
Exit Function
Err_AuditDelEnd:
Resume Exit_AuditDelEnd
End Function
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditBegin 'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID, bWasNewRecord)
Dim db As Database ' CurrentDb()
Dim sSQL As String 'Remove any cancelled update still in the tmp table.
Set db = CurrentDb()
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL
' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ""
db.Execute sSQL, dbFailOnError
End If
AuditEditBegin = True
Exit_AuditEditBegin:
Set db = Nothing
Exit Function
Err_AuditEditBegin:
Resume Exit_AuditEditBegin
End Function
Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's AfterUpdate event. Example:
' Call AuditEditEnd("Data Entry", "audTmpDataEntry", "audDataEntry", "ID", Me!ID, bWasNewRecord)
Dim db As Database
Dim sSQL As String
Set db = CurrentDb()
If bWasNewRecord Then ' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ""
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ""
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True
Exit_AuditEditEnd:
Set db = Nothing
Exit Function
Err_AuditEditEnd:
Resume Exit_AuditEditEnd
End Function
I added the following codes to the The Data Entry form:
Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.
Dim bWasNewRecord As Boolean
Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpDataEntry", "audDataEntry", Status)
End Sub
Private Sub Form_AfterUpdate()
Call AuditEditEnd("Data Entry", "audTmpDataEntry", "audDataEntry", "ID", Me!ID, bWasNewRecord)
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID, bWasNewRecord)
End Sub
Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("DataEntry", "audTmpDataEntry", "ID", Me!ID)
End Sub
Details are as follows:
the name of the table to be audited: Data Entry
the name of temp table: audTmpDataEntry
name of audit log table: audDataEntry
ID the primary key of the table being audited
I made the tmp and audit log table as follows:
In the Database window, Copied (Ctrl+C) and pasted (Ctrl+V) the table to be audited. Chose the "Structure Only" option so the data is not copied, named audTmpDataEntry
Opened the temp table in Design view. Removed all indexes, including the Primary Key. Changed the AutoNumber field type to a Number, Long Integer. Removed any Validation rules from the table and all its fields. (Note: the temp table has no primary key.)
Still in Design view of the temp table, inserted these three new fields at the top of the table (i.e. before other fields):
Field Name Field Type Size
audType Text 8
audDate Date/Time
audUser Text 20
Copied (Ctrl+C) and pasted (Ctrl+V) this table to the true audit log table, named audDataEntry.
Opened the audit table in design view. Inserted a new AutoNumber field, and marked it as Primary Key, named audID.
The Audit module trial is as follows:
' Purpose: Audit trail, to track Deletes, Edits, and Inserts.
' Does not audit any Cascading Updates/Deletes.
' Requirements: The table to be audited must have an AutoNumber primary key.
' You need a temp table containing the same fields as the table to be audited, plus:
' Field Name Type Size To Contain
' ---------- ---- ---- ----------
' audType Text 8 Delete, Insert, EditFrom or EditTo.
' audDate Date/Time Date and time of operation.
' audUser Text 20 =CurrentUser()
' In addition, you need an audit logging table with all these fields and an
' AutoNumber primary key.
' Result: The audit table will contain one record for each deletion or
' insertion, and two records for each edit (before and after).
' Delete Copy of the deleted record, marked "Delete".
' Insert Copy of the new record, marked "Insert".
' Change: Copy of the record before change, marked "EditFrom".
' Copy of the record after change, marked "EditTo".
' This approach, together with the sequential numbering of the
' AutoNumber in the audit table makes tampering with the audit
' log more detectable.
Option Compare Database
Option Explicit
Function AuditDelBegin(sTable As String, sAudTmpTable As String, _
sKeyField As String, lngKeyValue As Long) As Boolean
On Error GoTo Err_AuditDelBegin
'Purpose: Write a copy of the record to a tmp audit table.
' Copy to be written to real audit table in AfterDelConfirm.
'Arguments: sTable = name of table to be audited.
' sAudTmpTable = the name of the temp audit table.
' sKeyField = name of AutoNumber field in table.
' lngKeyValue = number in the AutoNumber field.
'Return: True if successful.
'Usage: Call from a form's Delete event. Example:
' Call AuditDelBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID)
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
Dim db As Database ' CurrentDb()
Dim sSQL As String ' Append query.
' Append record to the temp audit table.
Set db = CurrentDb
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & "
;"db.Execute sSQL, dbFailOnError
Exit_AuditDelBegin:
Set db = Nothing
Exit Function
Err_AuditDelBegin:
Resume Exit_AuditDelBegin
End Function
Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
On Error GoTo Err_AuditDelEnd
'Purpose: If the deletion was completed, copy the data from the
' temp table to the autit table. Empty temp table.
'Arguments: sAudTmpTable = name of temp audit table
' sAudTable = name of audit table
' Status = Status from the form's AfterDelConfirm event.
'Return: True if successful.
'Usage: Call from form's AfterDelConfirm event. Example:
' Call AuditDelEnd("audTmpDataEntry", "audDataEntry", Status)
Dim db As Database ' CurrentDb()
Dim sSQL As String ' Append query.
' If the Delete proceeded, copy the record(s) from temp table to delete table.
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
Set db = CurrentDb()
If Status = acDeleteOK Then
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
db.Execute sSQL, dbFailOnError
End If
'Remove the temp record(s).
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
AuditDelEnd = True
Exit_AuditDelEnd:
Set db = Nothing
Exit Function
Err_AuditDelEnd:
Resume Exit_AuditDelEnd
End Function
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditBegin 'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID, bWasNewRecord)
Dim db As Database ' CurrentDb()
Dim sSQL As String 'Remove any cancelled update still in the tmp table.
Set db = CurrentDb()
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL
' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & "
;"db.Execute sSQL, dbFailOnError
End If
AuditEditBegin = True
Exit_AuditEditBegin:
Set db = Nothing
Exit Function
Err_AuditEditBegin:
Resume Exit_AuditEditBegin
End Function
Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's AfterUpdate event. Example:
' Call AuditEditEnd("Data Entry", "audTmpDataEntry", "audDataEntry", "ID", Me!ID, bWasNewRecord)
Dim db As Database
Dim sSQL As String
Set db = CurrentDb()
If bWasNewRecord Then ' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & "
;"db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, CurrentUser() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & "
;"db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True
Exit_AuditEditEnd:
Set db = Nothing
Exit Function
Err_AuditEditEnd:
Resume Exit_AuditEditEnd
End Function
I added the following codes to the The Data Entry form:
Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.
Dim bWasNewRecord As Boolean
Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpDataEntry", "audDataEntry", Status)
End Sub
Private Sub Form_AfterUpdate()
Call AuditEditEnd("Data Entry", "audTmpDataEntry", "audDataEntry", "ID", Me!ID, bWasNewRecord)
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("Data Entry", "audTmpDataEntry", "ID", Me!ID, bWasNewRecord)
End Sub
Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("DataEntry", "audTmpDataEntry", "ID", Me!ID)
End Sub
