0

I was looking at a code of someone who used cn.BeginTrans right before deleting a Table. The code looked something like this

cn.BeginTrans
   //Update or delete table code
cn.CommitTrans

I played around with BeginTrans and UpdateTrans and I understand what it does. It is basically like version control where BeginTrans= git add and git commit and UpdateTrans= git push. At least this is how I understand it.

What I do not understand is the fact that Commitrans is used immediately after begintrans. If the code is committed according to this stackoverflow post there is no way to rollback.

Why not just delete or update the table without using the Trans method if you are going to Commit immediately?

braX
  • 11,506
  • 5
  • 20
  • 33
namko
  • 627
  • 1
  • 14
  • 27

1 Answers1

2

With a single delete or update in autocommit mode (the default), there is no need for the transaction as each statement is guaranteed to be all-or-none. However, in the case of multiple statements, it is important to use an explict transaction to ensure either all changes are made (committed) or none (rolled back) in order to maintain a consistent database. One would typically issue a rollback in a catch block (or use TransactionScope), to ensure changes are rolled back after errors.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I understand the use of the Begin and rollback Trans to catch error. Would you please clarify how it would be useful in the case of multiple statements. I thought in VBA every line is executed in order so there should be no danger of some statements executing before the other. – namko Dec 16 '18 at 09:50
  • @namko, the issue isn't with the order of operations but the action to take after an error. Consider the case of saving an order, which inserts a row into an `OrderHeader` table and a row into `OrderDetail` for each item. If the `Order` insert succeeds but one of the `OrderDetail` inserts fail, the incomplete order would be left in the database without the transaction. The transaction allows the entire order to be committed upon success of all inserts or the entire operation rolled back after an error so that there are no artifacts of the erred order left in the database. – Dan Guzman Dec 16 '18 at 12:39