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.

VB Code:
  1. 'Option One

  2.     objConn.BeginTrans

  3.     

  4.     sSQL = "UPDATE TableOne SET MyID = " & iNewID & " WHERE MyID = " & iOldID    

  5.     objCmd.ActiveConnection = objConn

  6.     objCmd.CommandText = sSQL

  7.     objCmd.CommandType = adCmdText

  8.     objCmd.Execute lRows1

  9.      

  10.      sSQL = "DELETE FROM TableTwo WHERE MyID = " & iOldID                        

  11.      objCmd.ActiveConnection = objConn

  12.      objCmd.CommandText = sSQL

  13.      objCmd.CommandType = adCmdText

  14.      objCmd.Execute lRows2

  15.      

  16.     If MsgBox(lRows1 + lRows2 & " Will be changed or deleted.", vbYesNo, App.Title) = vbYes Then

  17.         objConn.CommitTrans

  18.     Else

  19.         objConn.RollbackTrans

  20.     End If

  21.  
  22.  
  23.     'Option Two

  24.     objConn.BeginTrans

  25.     

  26.     sSQL = "UPDATE TableOne SET MyID = " & iNewID & " WHERE MyID = " & iOldID    

  27.     objCmd.ActiveConnection = objConn

  28.     objCmd.CommandText = sSQL

  29.     objCmd.CommandType = adCmdText

  30.     objCmd.Execute lRows1

  31.  
  32.     objConn.BeginTrans     

  33.      sSQL = "DELETE FROM TableTwo WHERE MyID = " & iOldID                        

  34.      objCmd.ActiveConnection = objConn

  35.      objCmd.CommandText = sSQL

  36.      objCmd.CommandType = adCmdText

  37.      objCmd.Execute lRows2

  38.      

  39.     If MsgBox(lRows1 + lRows2 & " Will be changed or deleted.", vbYesNo, App.Title) = vbYes Then

  40.         objConn.CommitTrans

  41.         objConn.CommitTrans

  42.     Else

  43.         objConn.RollbackTrans

  44.         objConn.RollbackTrans

  45.     End If

Greg