Is there a way to Test For open Transactions?

[shepherd]

I have a few procedures which uses transactions within access to ensure data consistency. What I'm wondering is, how can I test if a connection has an ongoing, uncommitted transaction or not. I'm trying to create a routine in my error handler to rollback any open transactions if any errors should occur, but I can't figure out how to test for the condition, and I get an error if I do a RollbackTran where there aren't transactions...

 

[PHV]

Why not do your RollbackTran call just after an On Error Resume Next ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shepherd]

hey,
thanks for the response. Yeah, that's actually how I've handled it in the past, but I was hoping there was a more graceful way (I always felt like this was kind of a hack). I'm thinking that this is probably the only way to do it though.

 

[FancyPrairie]

I don't believe there is a clean way. I usually set a boolean variable (in your case it might need to be a public) and when I start a transaction I set the value to true. Something like this

Dim bolBeginTrans as Boolean

On Error GoTo ErrHandler

bolBeginTrans = False
...
...
cnn.BeginTrans
bolBeginTrans = True
...
...
cnn.CommitTrans
...
ExitProcedure:
...
ErrHandler:

MsgBox err.number...
if (bolBeginTrans) then
cnn.RollBackTrans
bolBeginTrans = False
end if
Resume ExitProcedure