'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