Access 2010数据库中的审计跟踪

Question

我正试图在Access 2010数据库中创建审计跟踪。我在www.wvmitchell.com上找到了一些代码，除了一个问题外，它运行良好。它记录已更新但不是新记录或已删除记录的记录。将这些记录下来是非常重要的。以下是我使用的信息和代码：

Option Compare Database
Option Explicit
Sub TrackChanges(F As Form)
Dim ctl As Control, frm As Form
Dim MyField As String, MyKey As Long, MyTable As String
Dim db As DAO.Database, rs As DAO.Recordset
On Error Resume Next
Set frm = F
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl__ChangeTracker")
With frm
MyTable = .Tag
' find the primary key & its value, based on the Tag
For Each ctl In .Controls
If ctl.Tag = "PK" Then
MyField = ctl.Name
MyKey = ctl
Exit For
End If
Next ctl
For Each ctl In .Controls
' inspect only data-bound controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox
If Nz(ctl.ControlSource, "") > "" Then
' if changed, record both old & new values
If Nz(ctl.OldValue, "")<> Nz(ctl, "") Then
rs.AddNew
rs!FormName = .Name
rs!MyTable = MyTable
rs!MyField = MyField
rs!MyKey = MyKey
rs!ChangedOn = Now()
rs!FieldName = ctl.Name
If ctl.ControlType = acCheckBox Then
rs!Field_OldValue = YesOrNo(ctl.OldValue)
rs!Field_NewValue = YesOrNo(ctl)
Else
rs!Field_OldValue = Left(Nz(ctl.OldValue, ""), 255)
rs!Field_NewValue = Left(Nz(ctl, ""), 255)
End If
rs!UserChanged = UserName()
rs!CompChanged = CompName()
rs.Update
End If
End If
End Select
Next ctl
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
Private Function YesOrNo(v) As String
Select Case v
Case -1
YesOrNo = "Yes"
Case 0
YesOrNo = "No"
End Select
End Function

3. 存储结果的表格。对于文本字段，我在描述中指出了长度：

下面是一个VBA模块，它将为您创建表。

Option Compare Database
Option Explicit
Sub Create_tbl__ChangeTracker()
Dim db As DAO.Database
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim tdf As DAO.TableDef
'
Set db = CurrentDb
Set tdf = db.CreateTableDef("tbl__ChangeTracker")
With tdf
' ID is AutoNumber and Primary Key
Set fld = .CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
.Fields.Append fld
Set idx = .CreateIndex("ID")
idx.Fields = "ID"
idx.Primary = True
.Indexes.Append idx
'
' add remaining fields
Set fld = .CreateField("FormName", dbText, 64)
.Fields.Append fld
Set fld = .CreateField("MyTable", dbText, 64)
.Fields.Append fld
Set fld = .CreateField("MyField", dbText, 64)
.Fields.Append fld
Set fld = .CreateField("MyKey", dbText, 64)
.Fields.Append fld
Set fld = .CreateField("ChangedOn", dbDate)
.Fields.Append fld
Set fld = .CreateField("FieldName", dbText, 64)
.Fields.Append fld
Set fld = .CreateField("Field_OldValue", dbText, 255)
.Fields.Append fld
Set fld = .CreateField("Field_NewValue", dbText, 255)
.Fields.Append fld
Set fld = .CreateField("UserChanged", dbText, 128)
.Fields.Append fld
Set fld = .CreateField("CompChanged", dbText, 128)
.Fields.Append fld
Set fld = .CreateField("Action", dbtext, 64
.Fields.Append fld
End With
db.TableDefs.Append tdf
Set idx = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

添加这些对象后，按如下方式修改每个表单：1.设置表单的Tag属性=基础表的名称。2.识别表单后面数据的主键，并将Tag属性设置为"PK"(不带引号)。字段不必在表单上可见，它只需要在某个地方即可。3.添加

Form_BeforeUpdate event and invoke the tracking code using:
TrackChanges Me

4.如果您使用的是子表单，您还需要对每个子表单执行这三个步骤。

在我的数据库中，我向tbl_ChangeTracker添加了一个名为Action的文本字段。我需要知道如何编写代码来填充它。提前感谢您对我的帮助。

Answer 1

您已经编写了代码；

If Nz(ctl.ControlSource, "") > "" Then
' if changed, record both old & new values
If Nz(ctl.OldValue, "")<> Nz(ctl, "") Then

其中，它指定更改后是否同时记录旧值和新值。您没有任何内容可以指定添加新值时要执行的操作。

在您的表格更新前的程序中放置以下内容；

If Me.NewRecord Then
Call TrackChanges(me, "NEW")
Else
Call TrackChanges(me ,"EDIT")
End If

然后，通过您的审计过程，将其更改为接受另一个String类型的参数，例如；

Sub TrackChanges(F As Form, action As String)

然后在你想要的代码中；

Select Case action
Case is = "New"
' code here for adding new record, as an example
Case is = "Edit"
'your code as you have it above here for edits

如果你想跟踪删除，同样的事情在删除之前的事件；

Call TrackChanges(Me,"DELETE")

然后在您的TrackChanges过程中有另一个Case is="Delete"，然后是处理删除的代码。

这应该会让你走上正确的道路，然后用你的Action字段你就可以做到了！action=操作，这样你就知道它是编辑、添加还是删除等。

HTH标记

在你的评论后面编辑1，这样你就可以看到布局；Select Case代码将位于TrackChanges函数中。在"with Frm"之前放置所选案例。然后你想把它设置如下；

Select Case action
Case is = "Edit"
With frm 'etc all your code here for if you edit record
Case is = "new"
With frm 'etc all your code here for if adding new record
End select

如果这有意义的话，请告诉我，如果不是晚上给我开枪，如果你需要的话，我可以更深入地告诉你：)