Scope of transaction processing wrapper

[rdemyan via AccessMonster.com]

I have some unbound forms that I want to use transaction wrappers on when
updating the table. My question is on the scope of what transaction
processing will do. I'm going to keep it simple with the following code:


Code:
------------------------------------------------------------------------------
--

ws.BeginTrans
bInTrans = True

'Delete record from TableA
rs.Delete

'Now update TableB
Call WriteMasterTabl eRecord(Recordt oDelete, ValueA, ValueB)

'Now update some information in TableC
Call StatusAsMaster( MasterID, ValueD)

ws.CommitTrans
bInTrans = False
------------------------------------------------------------------------------
--


Again, the code has been simplified.

What I want to know is if the transaction wrapper for TableA also covers
TableB and TableC which are updated in other procedures. In my exit handler
there is the following:

If bInTrans = True Then
ws.Rollback
End If

So, if an error occurs, the Error_Handler is invoked and then the exit
handler. Will the Rollback statement roll back the changes to all three
tables (A, B and C) or will it only roll back Table A??

--
Message posted via http://www.accessmonster.com

[Allen Browne]

Re: Scope of transaction processing wrapper

The answer to your question depends on how you write the changes to the
other tables.

If you write the changes with something like:
db.Execute ...
where db is the database in the ws workspace, then they will rollback.

But they will not roll back if you make the changes with (say) RunSQL, or
through another Workspace variable, since these are outside of the scope of
your transaction.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdemyan via AccessMonster.c om" <u6836@uwe> wrote in message
news:5d2e8b9dbc 994@uwe...[color=blue]
>I have some unbound forms that I want to use transaction wrappers on when
> updating the table. My question is on the scope of what transaction
> processing will do. I'm going to keep it simple with the following code:
>
>
> Code:
> ------------------------------------------------------------------------------
> --
>
> ws.BeginTrans
> bInTrans = True
>
> 'Delete record from TableA
> rs.Delete
>
> 'Now update TableB
> Call WriteMasterTabl eRecord(Recordt oDelete, ValueA, ValueB)
>
> 'Now update some information in TableC
> Call StatusAsMaster( MasterID, ValueD)
>
> ws.CommitTrans
> bInTrans = False
> ------------------------------------------------------------------------------
> --
>
>
> Again, the code has been simplified.
>
> What I want to know is if the transaction wrapper for TableA also covers
> TableB and TableC which are updated in other procedures. In my exit
> handler
> there is the following:
>
> If bInTrans = True Then
> ws.Rollback
> End If
>
> So, if an error occurs, the Error_Handler is invoked and then the exit
> handler. Will the Rollback statement roll back the changes to all three
> tables (A, B and C) or will it only roll back Table A??[/color]

[rdemyan via AccessMonster.com]

Re: Scope of transaction processing wrapper

Allen:

Thank you for the response. I'm using different DAO workspace, database and
recordset variables in the procedures that update Tables B and C. I'm not
using db.Execute type statements, but instead rsXXX.AddNew or rsXXX.Edit and
rsXXX.Update.

Does this mean that I should pass the DAO variables that update TableA into
the procedures that update TablesB and C. Do I just pass in the ws and db
variables and then create a new recordset based on the SQL statement that is
appropriate for each procedure to accomplish that procedure's job?

If so, I assume that these DAO variables should still be closed in the
original module that created them (i.e. where TableA is updated).

Thanks.

Allen Browne wrote:[color=blue]
>The answer to your question depends on how you write the changes to the
>other tables.
>
>If you write the changes with something like:
> db.Execute ...
>where db is the database in the ws workspace, then they will rollback.
>
>But they will not roll back if you make the changes with (say) RunSQL, or
>through another Workspace variable, since these are outside of the scope of
>your transaction.
>[color=green]
>>I have some unbound forms that I want to use transaction wrappers on when
>> updating the table. My question is on the scope of what transaction[/color]
>[quoted text clipped - 35 lines][color=green]
>> handler. Will the Rollback statement roll back the changes to all three
>> tables (A, B and C) or will it only roll back Table A??[/color][/color]

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200603/1

[Allen Browne]

Re: Scope of transaction processing wrapper

Yes, that all sounds right.

Just pass the Database variable, and it will all rollback.

This article illustrates transactions:

http://allenbrowne.com/ser-37.html

It all happens in one procedure, so it does not illustrate passing the db,
but the technique is identical, and it does explain a couple of traps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdemyan via AccessMonster.c om" <u6836@uwe> wrote in message
news:5d32b3ce99 067@uwe...[color=blue]
> Allen:
>
> Thank you for the response. I'm using different DAO workspace, database
> and
> recordset variables in the procedures that update Tables B and C. I'm not
> using db.Execute type statements, but instead rsXXX.AddNew or rsXXX.Edit
> and
> rsXXX.Update.
>
> Does this mean that I should pass the DAO variables that update TableA
> into
> the procedures that update TablesB and C. Do I just pass in the ws and db
> variables and then create a new recordset based on the SQL statement that
> is
> appropriate for each procedure to accomplish that procedure's job?
>
> If so, I assume that these DAO variables should still be closed in the
> original module that created them (i.e. where TableA is updated).
>
> Thanks.
>
> Allen Browne wrote:[color=green]
>>The answer to your question depends on how you write the changes to the
>>other tables.
>>
>>If you write the changes with something like:
>> db.Execute ...
>>where db is the database in the ws workspace, then they will rollback.
>>
>>But they will not roll back if you make the changes with (say) RunSQL, or
>>through another Workspace variable, since these are outside of the scope
>>of
>>your transaction.
>>[color=darkred]
>>>I have some unbound forms that I want to use transaction wrappers on when
>>> updating the table. My question is on the scope of what transaction[/color]
>>[quoted text clipped - 35 lines][color=darkred]
>>> handler. Will the Rollback statement roll back the changes to all three
>>> tables (A, B and C) or will it only roll back Table A??[/color][/color]
>
> --
> Message posted via AccessMonster.c om
> http://www.accessmonster.com/Uwe/For...ccess/200603/1[/color]