RollbackTrans on .Execute Error


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

Advertisements

A

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
 
D

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)
 
A

Allen Browne

david said:
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.
 
D

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

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
 
Ad

Advertisements

G

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)
 
A

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

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top