Is there an active transaction?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everubody.

I wonder if there is a simpler to get to know if there is a currently active
transaction during execution. The only way I am able to get that information
is to declare a boolean variable inside the procedure or function where I
need a transaction, and then set that variable to True just after beginning a
transaction, set it to False just after committing the transaction. That way,
if a runtime occurs, I am able to know if there is any active transaction so
that I can roll it back. It works well, but I don't like this kind of
solution.

I was reading through the Workspace object documentation, but couldn't find
anything that can tell me if a given workspace has an active transaction at
any given moment. So I would like to ask you folks if anyone out there has a
better approach to face this sort of problem.

Thank you very much in advance.
Greetings from Portugal.
Reis Quarteu.
 
Hi, Reis.

If you're familiar with the ADO library, then there's an adErrInTransaction
Constant for the Error class. One could check that in the error handler and
procede accordingly.

Transactions should only include data transactions, not other executable VBA
statements. Otherwise, you'll find yourself having to code around possible
run-time errors. A common method of rolling back a single operation is to
use the Execute method of the Database Object, along with the dbFailOnError
argument. In the following example, the error handler could roll back the
transaction if an operation within the transaction failed for any reason,
because the dbFailOnError rolls back the currently executed SQL statement,
then jumps to the error handler. Of course, setting a flag when starting a
transaction would alert the error handler to do the roll back of the previous
operations in the transaction, so this code of yours would still be effective.

CurrentDb.Execute "INSERT INTO tblOrderDetails
"(ItemID, Amount, Discount, UnitPrice, Fee, InvoiceID) " & _
"SELECT ItemID, Amount, Discount, UnitPrice, Fee," & _
lngNewOrderID & _
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID.Value, dbFailOnError

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Reis Quarteu said:
Hi everubody.

I wonder if there is a simpler to get to know if there is a currently
active transaction during execution. The only way I am able to get
that information is to declare a boolean variable inside the
procedure or function where I need a transaction, and then set that
variable to True just after beginning a transaction, set it to False
just after committing the transaction. That way, if a runtime occurs,
I am able to know if there is any active transaction so that I can
roll it back. It works well, but I don't like this kind of solution.

I was reading through the Workspace object documentation, but
couldn't find anything that can tell me if a given workspace has an
active transaction at any given moment. So I would like to ask you
folks if anyone out there has a better approach to face this sort of
problem.
Click to expand...

For what it's worth, I don't know of a better way than the one you
describe: using a boolean flag. That's what I do. I suppose that an
alternative would be to Commit or Rollback unconditionally (whichever is
appropriate), and trap the error that will occur if you're not in a
transaction.
 
Back
Top