vba MS Access 表单绑定到 ADO 断开的记录集

MS Access Form Bound to ADO Disconnected Recordset

提问by dmaruca

I seem to be clueless on this subject. I can attach an ADO recordset to a form, but I am not sure how to handle updates. I don't want to only UpdateBatch, I want to be able to detect what was changed for logging purposes. Can anyone point me in the right direction?

我似乎对这个问题一无所知。我可以将 ADO 记录集附加到表单，但我不确定如何处理更新。我不想只使用 UpdateBatch，我希望能够检测出于日志记录目的而更改的内容。任何人都可以指出我正确的方向吗？

The supplied SQL contains one Key field which is an Autonumber named "ID".

提供的 SQL 包含一个 Key 字段，它是一个名为“ID”的自动编号。

Private Sub Form_Load()
    Dim rst as Object
    Set rst = CreateObject("ADODB.Recordset")
    rst.CursorLocation = adUseClient
    '...edit out connection
    rst.Open sql, mConnection, adOpenStatic, adLockBatchOptimistic
    set rst.ActiveConnection = Nothing
    Set Me.Recordset =  rst
End Sub 

''Edit records on the form and now click save
Private Sub cmdSave_Click()
    Dim rst As Object
    Set rst = Me.Recordset
    Set rst.ActiveConnection = GetConnection
    rst.UpdateBatch
    'How do I detect deleted, added, or modified records for logging? 
End Sub

采纳答案by HK1

You should be able to use the forms BeforeUpdate and AfterUpdate events to detect additions and edits. As far as Deletes go, you'll need to use the one of the forms delete events: BeforeDelConfirm, AfterDelConfirm or Delete.

您应该能够使用表单 BeforeUpdate 和 AfterUpdate 事件来检测添加和编辑。就删除而言，您需要使用以下形式的删除事件之一：BeforeDelConfirm、AfterDelConfirm 或 Delete。

The Dirty event is also handy when it comes to detecting when a user has started editing a record.

Dirty 事件在检测用户何时开始编辑记录时也很方便。

I think you really need to make your rst Recordset object a form level object instead of putting it in your Form's Load event.

我认为你真的需要让你的第一个 Recordset 对象成为一个表单级别的对象，而不是把它放在你的表单的 Load 事件中。

    Dim rst As Object

Private Sub Form_Load()
    Set rst = CreateObject("ADODB.Recordset")
    rst.CursorLocation = adUseClient
    '...edit out connection
    rst.Open sql, mConnection, adOpenStatic, adLockBatchOptimistic
    set rst.ActiveConnection = Nothing
    'You can close your connection object here now
    Set Me.Recordset =  rst
End Sub 

''Edit records on the form and now click save
Private Sub cmdSave_Click()
    Set rst.ActiveConnection = GetConnection
    rst.UpdateBatch
End Sub

Private Sub Form_Unload()
    'Offer to do batch update here if changes have been made to the recordset
    rst.Close
    Set rst = Nothing
End Sub

You might look into using an AuditTrail function to log changes. However, if the user doesn't perform the batch update, those changes won't actually be made to the database so I'm not sure exactly how you're going to log your changes in a simple, easy manner.

您可能会考虑使用 AuditTrail 函数来记录更改。但是，如果用户不执行批量更新，这些更改实际上不会对数据库进行，因此我不确定您将如何以简单、轻松的方式记录更改。

Here's some audit trail code that should work: http://www.everythingaccess.com/tutorials.asp?ID=Creating-an-Audit-Trail-(Source-Code)

这是一些应该工作的审计跟踪代码：http://www.everythingaccess.com/tutorials.asp?ID=Creating-an-Audit-Trail-(Source-Code )

I see that Mr. Fenton has questioned why you need a disconnected ADO recordset instead of using MS Access's built-in DAO binding. I do know there are certain situations where an ADO recordset makes sense but I think they are few and far between. Binding to recordsources such as XML files might be one example. I personally like to use it when for binding to a remote SQL Server. It works great for making Access talk to a SQL Server database on your web server out in the cloud. However, you can do this same thing with ODBC tables so there isn't really a compelling reason for using an ADO recordset except that managing DSN's or ODBC table links does have it's challenges.

我看到 Fenton 先生质疑为什么需要断开连接的 ADO 记录集而不是使用 MS Access 的内置 DAO 绑定。我确实知道在某些情况下 ADO 记录集是有意义的，但我认为它们很少见。绑定到记录源（例如 XML 文件）可能是一个示例。我个人喜欢在绑定到远程 SQL Server 时使用它。它非常适合让 Access 与云中 Web 服务器上的 SQL Server 数据库通信。但是，您可以对 ODBC 表做同样的事情，因此除了管理 DSN 或 ODBC 表链接确实存在挑战之外，并没有真正令人信服的理由使用 ADO 记录集。

Edit1:
In answer to the OP's concerns about events not catch mass deletions and mass pastes. The Delete event fires for each record selected for deletion and the AfterDelConfirm event fires after the user has pressed "Yes". With paste you are not so lucky as there is no event that fires after the user confirms the paste. One work-around is to disabled additions in the form and use some other method to insert new records.

编辑 1：
为了回应 OP 对事件的关注，无法捕获大量删除和大量粘贴。Delete 事件为选择删除的每条记录触发，AfterDelConfirm 事件在用户按下“是”后触发。使用粘贴，您就没有那么幸运了，因为在用户确认粘贴后不会触发任何事件。一种解决方法是禁用表单中的添加项并使用其他方法插入新记录。

Another option you might look into is using ADO recordset events. It appears the events will likely do everything except one very critical thing - return a bookmark or primary key for each record that is being edited, deleted, or inserted.

您可能会考虑的另一个选项是使用 ADO 记录集事件。除了一件非常关键的事情 - 为正在编辑、删除或插入的每条记录返回一个书签或主键之外，这些事件可能会做所有事情。

Yet a third option is to set a DateTimeModified for each record. You could then use code at almost any time to iterate through the recordset and log the changes that haven't been logged yet. Simply create a recordset clone and use the recordset's Filter method, something like this:

然而，第三个选项是为每条记录设置一个 DateTimeModified。然后，您几乎可以随时使用代码遍历记录集并记录尚未记录的更改。只需创建一个记录集克隆并使用记录集的 Filter 方法，如下所示：

rst.Filter "DateTimeModified > " & LastLoggedDateTime

Now iterate through the filtered recordset and log the records. If necessary you could possibly keep a copy of the original recordset in memory (read only) and use it for comparisons. Take a look at this post: compare two record sets in vb6

现在遍历过滤的记录集并记录记录。如有必要，您可以在内存中保留原始记录集的副本（只读）并将其用于比较。看看这个帖子：比较vb6中的两个记录集

I do agree that there is no real simple way of doing what you're trying to do. It appears to be fairly complex.

我同意没有真正简单的方法来做你想做的事情。它似乎相当复杂。