绑定到ADO断开记录集的MS Access表单 [英] MS Access Form Bound to ADO Disconnected Recordset

问题描述

我似乎对这个问题一无所知.我可以将ADO记录集附加到表单，但是不确定如何处理更新.我不想仅使用UpdateBatch，而是希望能够检测到出于日志记录目的所做的更改.谁能指出我正确的方向?

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?

提供的SQL包含一个密钥"字段，该字段是一个名为"ID"的自动编号.

The supplied SQL contains one Key field which is an Autonumber named "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

推荐答案

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

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.

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

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

我认为您真的需要使第一个Recordset对象成为表单级别的对象，而不是将其放入Form的Load事件中.

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.

    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

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

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.

下面的一些审计跟踪代码应该起作用: http://www.everythingaccess. com/tutorials.asp?ID =创建审核跟踪(源代码)

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

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

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.

为了回答OP对事件的担忧，无法捕获大量删除和大量粘贴.用户选择是"后，将为每个要删除的记录触发Delete事件，并触发AfterDelConfirm事件.使用粘贴时，您不会那么幸运，因为在用户确认粘贴后不会触发任何事件.一种解决方法是禁用表单中的添加内容，并使用其他方法插入新记录.

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.

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

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.

第三个选项是为每个记录设置一个DateTimeModified.然后，您几乎可以随时使用代码遍历记录集并记录尚未记录的更改.只需创建一个记录集克隆并使用记录集的Filter方法，就像这样:

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:

rst.Filter "DateTimeModified > " & LastLoggedDateTime

现在遍历过滤的记录集并记录记录.如有必要，您可以在内存中保留原始记录集的副本(只读)，并将其用于比较.看一下这篇文章:比较vb6中的两个记录集

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

我同意，没有真正简单的方法可以完成您要尝试做的事情.它似乎很复杂.

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

这篇关于绑定到ADO断开记录集的MS Access表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！