On Thu, 30 Dec 2004 23:00:45 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in
news:41******* *************** *@per-qv1-newsreader-01.iinet.net.au :
Another possibility might be to use replication, and let Access
handle the issues of synchronizing the various user's copies back
to the master.
I see no evidence in the original post that replication would solve
any problems here whatsoever. The point of transactions is that if
any part of it fails, you want the whole thing to be rolled back.
Replication can't do that at all.
In short, it is generally not worth the effort in an Access
application.
I disagree. I have often wrapped add operations of master/child data
structures in transactions. Another place where I use transactions
is when posting batches of invoices (many of the apps I've created
have monthly billing cycles, and most invoices are created in a
batch). Sometimes I've rolled back the entire batch and sometimes I
simply roll back each individual invoice that fails to completely
post.
I've never used transactions for editable data, though. There I just
don't think it's doable, for all the reasons you cite.
It is doable enough so long as there are no 1-M relationships involved. I
realize that's an unusual case, but it's not rare, and I've dealt with it in
several times in the last week.
I have been given a highly normalized schema that has things like a junction
table with start-end times for a supplier's street or postal address, and each
street or postal address has a relationship to an address record containing
info common to both street and postal address. When creating a new address,
we wish to add the Address record, the street or postal address record, and
the supplier-street-address record or supplier-postal-address record. If any
of these actions fail, we want to roll the whole thing back.
This is a case where an unbound form and DAO transactions works just great -
no temporary tables required. I've come up with a nice class module helper
for this where the form passes a list of tables to be inserted into (to
specify the order), and the tag property of each "bound" control contains
<tablename>.<fi eldname> or *.<fieldname>. The form then calls a Save method
of the class, passing itself as an argument. The class is able to identify
Autonumber fields which it reads rather than saves (to a hidden control), so
they are available to be written to subsequent tables in the list. It works
like a charm.
I am right with you on how to do things like orders and invoices, or even
complex, custom search criteria sets with 1-M or more complex structures.
These should be entered into a pending data table, then posted using
transactions. I used to use tables in a temporary database for these sorts of
things, but have come to realize that there's almost always some reason to
want the input data stored at least semi-permanently, and it can always be
purged later.