vba - MS Access Form Bound to ADO Disconnected Recordset

Question

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?

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

Answer 1

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.

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

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

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.

Here's some audit trail code that should work: 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.

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.

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.

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

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.