Transactions across subroutines in ADO

[jonefer]

I tried the ADO group and was redirected back to Office solutions - Hope this
is correcct.

I'm working with an ADP.

I'd like to start a transaction a 'New Record' button on my MS Access form
and then End the transaction several pages later after adding many children
records.

The cmdNewRecord buton creates a parent - core record first and has a
'continue' button so that the user can be directed to a different tab on the
form.

On that tab are several 'Add buttons' along with more textboxes to add
children records.


At this point, the transaction would still be going. I thought of having a
module level variable to keep tabs of if a Transaction started (from the
first routine).. is this the appropriate way to do it?...

Having just started this concept, I haven't seen any examples out there that
show outside routines taking place while a transaction from another routine
has started. Could someone show me an example of the structure with using
the first connection, in this case CurrentProject, all the way through to the
commit or rollback?

 

[Jamie Collins]

I'm working with an ADP.

I'd like to start a transaction a 'New Record' button on my MS Access form
and then End the transaction several pages later after adding many children
records.

The cmdNewRecord buton creates a parent - core record first and has a
'continue' button so that the user can be directed to a different tab on the
form.

On that tab are several 'Add buttons' along with more textboxes to add
children records.

At this point, the transaction would still be going. I thought of having a
module level variable to keep tabs of if a Transaction started (from the
first routine).. is this the appropriate way to do it?...

Agreed, ADO doesn't do the best job of exposing transaction states but
I believe it tries to simplify things (for better or for worse). For
instance, Jet transactions can be nested (and is supported in ADO)
whereas other SQL DBMSs have the concept of save points (which neither
Jet nor ADO supports -- who says ADO wasn't designed for Jet <g>?!) so
you generally get zero or one transaction per Connection.

In common with many ADO teams, we have 'wrapper' classes for ADO
object's and we've added a transaction state enum to ours, which is
maintained when the class's BeingTrans, RollbackTrans etc methods are
called. Alternatively, you can safely (AFAIK) test a Connection
object's transaction state by attempting to begin a transaction e.g.
(aircode):

On Error Resume Next
m_con.BeginTrans
If Err.Number = 0 Then
m_con.RollbackTrans
TransactionState = jcTransactionStateNotStarted
ElseIf Err.Number = -2147168237 Then
TransactionState = jcTransactionStateStarted
etc

Obviously the above logic doesn't work in Jet due to its nested
transactions model!

Jamie.

--

 

[jonefer]

I managed to get it to work...
Though I'm using a module level variable for the connection.


dim cnMain as New ADODB.connection
Set cnMain = CurrentProject.Connect

Same with any recordset

I have a button on the form that does a cnMain.Rollback and closes any and
all recordsets if the user decides to cancel and any time an unexpected error
happens while the boolean variable bInTransaction =True.

The only problem that I am experiencing is if I have a rowsource SQL String
for say- a combo box that is changed in the ON FOCUS event, It returns a
Timeout Expired. But If I'm not in a transaction, it works. ??

I tried using adLockOptimistic, adLockReadOnly, but to no avail.

Hopefully what I have so far will work with multiple users.