are Transactions necessary with Forms?

[RC]

I have a form where the user types the shipping container number into
a box and then types the Pallet number into a combo box. The
AfterUpdate event for the combo box runs the following code which
finds every table row that has that pallet number in it and sets the
container number in the next column to match the container number the
user typed into the form. About 15 rows are changed because there are
about 15 boxes in each pallet.

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE Products " & "SET
Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] " & "WHERE
(([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]));")
DoCmd.SetWarnings True

I am using Access 2002. In the database title bar it says "(Access
2000 file format)". In the properties of the form the record source
for the form is the table with all the box and pallet numbers in it.
The database is split and the frontend and backend are both on the
same PC. The database runs only on this PC, it does not run over a
network.

My question is: I this code relatively safe? Do I need to wrap the
procedure in a transaction? I can't tell from all the transaction
messages in the group if I should use a transaction in this case or
not or even whether the form itself handles a type of transaction and
a transaction in code might not work or might mess things up. Should
I add "dbFailOnError" after my UPDATE code?

[Tom van Stiphout]

On 29 Aug 2004 08:16:54 -0700, rc*********@yahoo.com (RC) wrote:

On a single action query, transactions are useless. She who uses them
demonstrates she doesn't know how to use them.

If you were to use the Execute method to run your sql statement, yes,
the dbFailOnError flag is a good idea, so you are notified when an
error occurs. This has nothing to do with transactions though.

Transactions only are relevant if more than one action query is run.
The classic example is to run one query to debit an account, followed
by another query to credit another account by the same amount, thus
transferring money from one account to another. You want such code to
be atomic, meaning all is successful, or nothing happened.

-Tom.

I have a form where the user types the shipping container number into
a box and then types the Pallet number into a combo box. The
AfterUpdate event for the combo box runs the following code which
finds every table row that has that pallet number in it and sets the
container number in the next column to match the container number the
user typed into the form. About 15 rows are changed because there are
about 15 boxes in each pallet.

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE Products " & "SET
Products.ContainerNumberProductsTable =
[Forms]![ContainerAssociationForm]![GETContainerNumber] " & "WHERE
(([Products.PalletNumberProductsTable]=[Forms]![ContainerAssociationForm]![PalletNumberContainerFormComboBox]));")
DoCmd.SetWarnings True

I am using Access 2002. In the database title bar it says "(Access
2000 file format)". In the properties of the form the record source
for the form is the table with all the box and pallet numbers in it.
The database is split and the frontend and backend are both on the
same PC. The database runs only on this PC, it does not run over a
network.

My question is: I this code relatively safe? Do I need to wrap the
procedure in a transaction? I can't tell from all the transaction
messages in the group if I should use a transaction in this case or
not or even whether the form itself handles a type of transaction and
a transaction in code might not work or might mess things up. Should
I add "dbFailOnError" after my UPDATE code?

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:i5********************************@4ax.com:

On a single action query, transactions are useless. She who uses
them demonstrates she doesn't know how to use them.

Er, that's not true, given that dbFailOnError does not necessarily
roll back the whole update in newer versions of Access.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

On Mon, 30 Aug 2004 18:41:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I've never seen that documented. Where did you find that?
' air code
on error resume next
CurrentDb.Execute "myactionquery", dbFailOnError
if err.number<>0 then
Msgbox "There is a chance some of the action was not rolled back"
end if

-Tom.

Tom van Stiphout <no*************@cox.net> wrote in
news:i5********************************@4ax.com :

On a single action query, transactions are useless. She who uses
them demonstrates she doesn't know how to use them.

Er, that's not true, given that dbFailOnError does not necessarily
roll back the whole update in newer versions of Access.

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:t4********************************@4ax.com:

On Mon, 30 Aug 2004 18:41:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Tom van Stiphout <no*************@cox.net> wrote in
news:i5********************************@4ax.co m:

On a single action query, transactions are useless. She who uses
them demonstrates she doesn't know how to use them.

Er, that's not true, given that dbFailOnError does not necessarily
roll back the whole update in newer versions of Access.

I've never seen that documented. Where did you find that?
' air code
on error resume next
CurrentDb.Execute "myactionquery", dbFailOnError
if err.number<>0 then
Msgbox "There is a chance some of the action was not rolled
back"
end if

Allen Browne brought this to my attention and pointed me to this
article of his:

http://members.iinet.net.au/~allenbrowne/ser-37.html

There it says:

dbFailOnError without a transaction is not enough. In Access 95
and earlier, dbFailOnError rolled the entire operation back, and
the Access 97 help file wrongly claims that is still the case.
(There is a correction in the readme.) FromAccess 97 onwards,
dbFailOnError stops further processing when an error occurs, but
everything up to the point where the error occurred is
committed.

I don't know if Allen can document it or not, but if Allen Browne
says it, I believe it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc