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.
推荐答案
您已编写代码；
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
然后将您要审计的事物的过程更改为接受另一个String类型的参数，例如；
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 = ="Delete"，然后是处理删除的代码.
Then in your TrackChanges procedure have another Case is = "Delete" then the code for handling the delete.
这应该将您设置在正确的路径上，然后在操作"字段中执行！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，这样您可以看到布局； 选择案例代码将在您的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屋！
