RollbackTrans on .Execute Error

  • Thread starter Thread starter dch3
  • Start date Start date
D

dch3

Is it neccessary to explicity rollback a transaction if the .Execute method
fails? My thinking is yes, however in the scenario that I have I'm only
dealing with a single .Execute statement that inserts a single record into a
table.
 
If you are inserting a single record, and it fails, there is no need to roll
back.

You might still want to use a transaction, e.g. go give the user the option
to change their mind (even if it's not going to fail.)

For an example of using transactions as a pair of operations (copy +
delete), see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
 
Transactions are slightly broken against MDB files (theoretically
you can get partial commits even though the transaction fails), and
badly broken against ODBC linked tables (transactions that
include more than one action deadlock in Access 2K+).

If you are thinking of using transactions, you should consider
going all the way to SQL Server stored procedures.

(david)
 
david said:
Transactions are slightly broken against MDB files (theoretically
you can get partial commits even though the transaction fails)
Click to expand...

That's not really correct.

If you *don't* use a transaction around an action query, the records
affected before the erorr occurred are committed in some versions of Access.
But if you use a transaction, you can then commit or rollback.
 
My question though is if I'm just dealing with a single action query and it
craps out do I need to explicity execute a Rollback? Obviously if multiple
Executes are involved then I a RollBack would be neccessary because 3 of 5
succeed and its an all or nothing deal.
 
The situation is confused by the poor documentation
from MS. Here is what the A97 help file had to say
about MaxLocksPerFile:

" This setting prevents transactions in Microsoft Jet from exceeding
the specified value. If the locks in a transaction attempts to exceed
this value, then the transaction is split into two or more parts and
partially committed."

And this was in the A97 readme, and subtly hidden in the
A2K help file:

"dbFailOnError No Longer Rolls Back a Transaction"

(david)
 
Right, David.

As I read that, if you Execute with dbFailOnError but without an explicit
transaction, A97 did not rollback the way earlier versions did.
 
As I read that, if you Execute with dbFailOnError but without an explicit
transaction, A97 did not rollback the way earlier versions did.
Click to expand...

Yes, but what about the other point? Does Access do partial
commits for large transactions, or is that an error in the help files?

(david)
 
Back
Top