RollbackTrans on .Execute Error

[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.

 

[Allen Browne]

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

 

[david]

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)

 

[Allen Browne]

Transactions are slightly broken against MDB files (theoretically
you can get partial commits even though the transaction fails)

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.

 

[dch3]

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.

 

[Allen Browne]

Any code that begins a transaction *must* either commit or rollback.

Otherwise you leave an uncommitted transaction hanging when you exit the
procedure.

That's Trap #4 in the article I referred you to at:
http://allenbrowne.com/ser-37.html

 

[Guest]

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)

 

[Allen Browne]

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.

 

[david]

As I read that, if you Execute with dbFailOnError but without an explicit

transaction, A97 did not rollback the way earlier versions did.

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)

 

[Allen Browne]

Fair question, David.
I've never tested that.