BeginTrans - CommitTrans

[gdebacker]

I'm using an ADO 2.5 reference/VB6/Access2

I'm a little confused about the proper way to use the BeginTrans, CommitTrans, and RollBackTrans in my situation. In the ADO help file on the MSDN CD they show a situation where you open a record set, then loop through and make changes to some records, and then ask the user to CommitTrans.

In my situation I need to UPDATE records in one table and then DELET records in another table. Would I have one transaction with one set of BeginTrans-CommitTrans, or should I have Two sets of BeginTrans - CommitTrans, one nested inside the other.

'Option One
    objConn.BeginTrans
    
    sSQL = "UPDATE TableOne SET MyID = " & iNewID & " WHERE MyID = " & iOldID    
    objCmd.ActiveConnection = objConn
    objCmd.CommandText = sSQL
    objCmd.CommandType = adCmdText
    objCmd.Execute lRows1
     
     sSQL = "DELETE FROM TableTwo WHERE MyID = " & iOldID                        
     objCmd.ActiveConnection = objConn
     objCmd.CommandText = sSQL
     objCmd.CommandType = adCmdText
     objCmd.Execute lRows2
     
    If MsgBox(lRows1 + lRows2 & " Will be changed or deleted.", vbYesNo, App.Title) = vbYes Then
        objConn.CommitTrans
    Else
        objConn.RollbackTrans
    End If
 
 
    'Option Two
    objConn.BeginTrans
    
    sSQL = "UPDATE TableOne SET MyID = " & iNewID & " WHERE MyID = " & iOldID    
    objCmd.ActiveConnection = objConn
    objCmd.CommandText = sSQL
    objCmd.CommandType = adCmdText
    objCmd.Execute lRows1
 
    objConn.BeginTrans     
     sSQL = "DELETE FROM TableTwo WHERE MyID = " & iOldID                        
     objCmd.ActiveConnection = objConn
     objCmd.CommandText = sSQL
     objCmd.CommandType = adCmdText
     objCmd.Execute lRows2
     
    If MsgBox(lRows1 + lRows2 & " Will be changed or deleted.", vbYesNo, App.Title) = vbYes Then
        objConn.CommitTrans
        objConn.CommitTrans
    Else
        objConn.RollbackTrans
        objConn.RollbackTrans
    End If

Greg

[Ecniv]

Just the one should do it

All the transactions do is record the changes, so as long as you aren't changing lots of data it should cope with it. The idea being if something goes wrong you roll back the previous data before you changed it. So processing your records then deleting them in the same transcaction should be ok.

I'd suggest (if you have the time) doing both one for it all and two and comparing the results.

Regards

Vince

[gdebacker]

I'd suggest (if you have the time) doing both one for it all and two and comparing the results.

Thanks for the feedback. I probably will test. I'm not dealing with a lot of data, and this is a single user app, so my main concern was finding the "proper" way to do it. It seems that sometimes there is more than one way to do things but only one preferred method.

Greg