Access 2010 数据库中的审计跟踪 [英] Audit trail in Access 2010 database
问题描述
我正在尝试在 Access 2010 数据库中创建审计跟踪.我在 www.wvmitchell.com 上找到了一些代码，除了一个问题外，它运行良好.它记录更新的记录，但不记录新记录或删除的记录.将这些记录下来非常重要.以下是我用到的资料和代码:
I am trying to create an audit trail in an Access 2010 database. I found some code on www.wvmitchell.com and it works well except for one issue. It records records that are updated but not new records or deleted records. It is very important that those are recorded. The following is the information and code that I used:
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
- 用于存储结果的表.对于文本字段，我已经在说明中指明了长度:
这是一个 VBA 模块，它将为您创建表格.
Here is a VBA module that will create the table for you.
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. 添加
After adding these objects, modify each form as follows: 1. Set the Tag property for the form = the name of the underlying table. 2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere. 3. Add the
Form_BeforeUpdate event and invoke the tracking code using:
TrackChanges Me
4.如果您使用子表单，则还需要为每个子表单执行这三个步骤.
4. If you are using subforms, you'll need to perform these three steps for each subform as well.
在我的数据库中，我向 tbl_ChangeTracker 添加了一个名为 Action 的文本字段.我需要知道如何编写代码来填充它.在此先感谢我得到的任何帮助.
In my database I added a text field called Action to the tbl_ChangeTracker. I need to know how to write the code to populate it. Thanks in advance for any help I get.
推荐答案
你的代码已经写好了 ;
You have your code written ;
If Nz(ctl.ControlSource, "") > "" Then
' if changed, record both old & new values
If Nz(ctl.OldValue, "")<> Nz(ctl, "") Then
它指定是否更改记录旧值和新值的位置.您没有任何内容可以指定添加新值时要执行的操作.
Where it specifies if changed record both old and new values. You have nothing in there to specify what to do when adding new values.
在您之前的表单更新程序中放置以下内容；
In your before update procedure of the form place the following ;
If Me.NewRecord Then
Call TrackChanges(me, "NEW")
Else
Call TrackChanges(me ,"EDIT")
End If
然后通过您的审计程序将其更改为接受另一个字符串类型的参数，例如；
Then with your procedure for the audit thing change it to accept another argument of type String so for instance;
Sub TrackChanges(F As Form, action As String)
然后在你想要的代码中；
Then in your code you want to have;
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
如果要跟踪删除，请在删除前事件中进行相同的操作；
If you want to track deletes, same sort of thing in the before delete event;
Call TrackChanges(Me,"DELETE")
然后在您的 TrackChanges 过程中有另一个 Case is = "Delete" 然后是处理删除的代码.
Then in your TrackChanges procedure have another Case is = "Delete" then the code for handling the delete.
这应该让您走上正确的道路，然后您可以使用 Action 字段执行 !action = action 以便您知道它是编辑添加或删除等.
This should set you on the right path, then with your Action field you can do !action = action so you know if it's edit add or delete etc.
HTH标记
在您的评论后编辑 1，以便您可以看到布局；Select Case 代码将在您的 TrackChanges 函数中.在with Frm"之前放置选择案例.那么你要设置如下；
Edit 1 following your comment so you can see the layout; The Select Case code would be in your TrackChanges function. Before 'with Frm' place the select case. Then you want to set it out as follows;
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
让我知道这是否有意义，如果没有，请给我一个下午，如果您需要，我可以更深入地与您交谈:)
Let me know if that makes sense, if not shoot me a pm and I can talk you through it in more depth if you need :)
这篇关于Access 2010 数据库中的审计跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
