vba MS Access“记录更改”日志

MS Access "record changes" log

提问by Patrick Honorez

Whenever a record is deleted or updated on a form, I want to save its old values in a history table (let's call it Revised). I guess I have to do the following:

每当表单上的记录被删除或更新时，我想将其旧值保存在历史表中（我们称之为修订版）。我想我必须执行以下操作：

For record changes:

对于记录更改：

• use the BeforeUpdate event to save the data somewhere(collection ? array ? or to a recordset -the Revised table- without saving-yet ?)
• use the AfterUpdate event to add/save that data to the Revised table

• 使用 BeforeUpdate 事件将数据保存在某处（集合？数组？或记录集 - 修订后的表 - 尚未保存？）
• 使用 AfterUpdate 事件将该数据添加/保存到修订表

For Deletions:

对于删除：

• use the OnDelete event to save the data - but again how ? several records could be deleted at once since the form (a subform in fact) is in datasheet view
• use the AfterDelConfirm to add that data to the Revised table.

• 使用 OnDelete 事件保存数据 - 但又如何？由于表单（实际上是一个子表单）在数据表视图中，因此可以一次删除多个记录
• 使用 AfterDelConfirm 将该数据添加到修订表中。

Do you have any clues, comments or links for this ?
This is all in a "pure Access" (no SQL Server) at the moment. Many thanks !

您对此有任何线索、评论或链接吗？
目前，这一切都在“纯访问”（无 SQL Server）中。非常感谢 ！

---

---

Edit: as usual, properly asking the question gaves me ideas:

编辑：像往常一样，正确地提出问题给了我一些想法：

option 1

选项1

use the BeforeUpdate or the OnDelete to build the SQL statement, and use the AfterUpdate or the AfterDelConfirm to Execute the SQL statement. But that won't work for multiple deletions ?

使用 BeforeUpdate 或 OnDelete 构建 SQL 语句，使用 AfterUpdate 或 AfterDelConfirm 执行 SQL 语句。但这不适用于多次删除？

option 2

选项 2

have the Revised recordset defined ta form level, insert the record "Before" but only Update "After". Again, problem with multiple deletes.

将修订记录集定义为表单级别，插入记录“之前”但只更新“之后”。再次，多次删除的问题。

回答by mwolfe02

I've successfully used a variation of Allen Browne's approach in a couple of different projects. Check out his website for more details:

我已经在几个不同的项目中成功地使用了 Allen Browne 方法的变体。查看他的网站了解更多详情：

Creating an Audit Log

创建审计日志

His solution uses temp tables and four generic function calls to handle the issue with multiple deletes.

他的解决方案使用临时表和四个通用函数调用来处理多次删除的问题。

回答by mwolfe02

Another approach I have considered more recently, but have not had an opportunity to actually implement, would be to use transactions to perform the change tracking. The basic algorithm would be:

我最近考虑过但没有机会实际实施的另一种方法是使用事务来执行更改跟踪。基本算法是：

1. use BeginTrans on the workspace prior to making any changes
2. in the OnDelete event
   • perform the deletions in code executing Delete queries against the workspace from step 1
   • add a record to your change auditing table
3. in the BeforeDelConfirm event
   • set Cancel = True
   • display your own Confirmation dialog
   • if user confirms then CommitTrans on workspace
   • otherwise Rollback the transaction on the workspace

1. 在进行任何更改之前在工作区上使用 BeginTrans
2. 在 OnDelete 事件中
   • 在从步骤 1 开始对工作区执行删除查询的代码中执行删除
   • 将记录添加到您的变更审计表
3. 在 BeforeDelConfirm 事件中
   • 设置取消 = 真
   • 显示您自己的确认对话框
   • 如果用户确认然后 CommitTrans 在工作区
   • 否则回滚工作区上的事务

Similar approach for Updates/Inserts. This would avoid the need for temporary tables/arrays/collections, etc. but I haven't fully thought through everything. The devil may be in the details.

更新/插入的类似方法。这将避免对临时表/数组/集合等的需要，但我还没有完全考虑清楚。魔鬼可能在细节中。

回答by Philippe Grondier

An "easy" and generic solution, which could be implemented for multiple tables, would be to have a tracking table, made of the following:

一个“简单”和通用的解决方案，可以为多个表实现，是有一个跟踪表，由以下组成：

Track_Table
==================================================
id_track as primary key
id_table as name of the table which has been updated
id_primaryKey as the record identifier (the PK of the updated record)
changeType, being either DEL or UPDATE
changeDate, as dateTime value
fieldName, as text
oldValue, as text or memo
newValue, as text or memo

if you have to identify the user who did the update, just add

如果您必须确定进行更新的用户，只需添加

userId

in your table ...

在你的桌子上...

You could then create some generic "before update" and "after update functions" to be called on selected form's beforeUpdate and afterUpdate events. The beforeUpdate fonction will store the old value in a variable, while the afterUpdate function will populate the missing data and insert a new record in the track table.

然后，您可以创建一些通用的“更新前”和“更新后函数”，以在选定表单的 beforeUpdate 和 afterUpdate 事件上调用。beforeUpdate 函数将旧值存储在变量中，而 afterUpdate 函数将填充丢失的数据并在轨道表中插入新记录。

You will have to find a way to find out the right\corresponding table name and field name. This could be difficult if you are using views or field aliases to display your data in forms.

您将不得不找到一种方法来找出正确的\对应的表名和字段名。如果您使用视图或字段别名在表单中显示数据，这可能会很困难。

Of course, all tables to be followed must have a primary key so you can follow changes at the record level. PKs set on multiple fields will surely be problematic ....

当然，所有要跟踪的表都必须有一个主键，以便您可以跟踪记录级别的更改。在多个字段上设置的 PK 肯定会出现问题....

oldValues and newValues will have to be converted as text so you can store them in a text or memo field

oldValues 和 newValues 必须转换为文本，以便您可以将它们存储在文本或备注字段中