By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Which way to have transactions?

P: n/a
So, chosen the interface for my app, finally I got stuck with transactions.
Boy!, how many ways of doing the same job are there?! Any suggestion so I
could find myself in the right direction? Pasting some code would be
appreciated.

My goal is updating several tables and rollback if any of the updates fail.
Not much, hmm? I've seen in this NG may discussions (I even gave a try on
the MS Access help, what a loss of time!!!) but couldn't decide between
which technique.

Cheers,
RV
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Not as easy as you might hope for.

For a single form (no subform), it is possible to:
- open a transaction;
- open a recordset;
- open a form;
- set the Recordset of the form to the recordset opened in code;
- edit/insert/delete records in the form;
- commit/rollback the transaction.

There are several practical issues with trying to do that, e.g.:
- It does not work for subforms that have a
LinkMasterFields/LinkChildFields, since they load records each time you move
record in the main form.
- Expect some instabilities (crashing Access).
- Test what happens if multiple users have overlapping transactions with
some committing and some rolling back.

An alternative approach would be to copy the records into a temp table, and
edit/append/delete on that. This still leaves you with the same problem of
trying to synchronize multiple simultaneous changes.

Another possibility might be to use replication, and let Access handle the
issues of synchronizing the various user's copies back to the master.

In short, it is generally not worth the effort in an Access application.

--
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.

"Raposa Velha" <jm******@hotmail.com> wrote in message
news:41**********************@news.telepac.pt...
So, chosen the interface for my app, finally I got stuck with
transactions. Boy!, how many ways of doing the same job are there?! Any
suggestion so I could find myself in the right direction? Pasting some
code would be appreciated.

My goal is updating several tables and rollback if any of the updates
fail. Not much, hmm? I've seen in this NG may discussions (I even gave a
try on the MS Access help, what a loss of time!!!) but couldn't decide
between which technique.

Cheers,
RV

Nov 13 '05 #2

P: n/a
My preferred method is to use DAO.

First, you'll want a separate workspace for the transaction, so nothing
outside of the code in question is accidentally tangled up with what you're
doing. Generally, you'll want to do this using DefaultWorkspaceClone, so you
get the same user credentials and permissions as the current user of the app.

Next, if the back end is now or might be a database server, you might want to
set the IsolateODBCTrans property of the new workspace object to True. This
causes a separate database connection to be used for transactions in the
workspace (again, preventing confusion if other database actions might
overlap).

Now, you need a reference to the database opened via the new workspace, so get
the full path of the database using CurrentDb.Name, then use the OpenDatabase
method of the new workspace object, passing the database path you got from
CurrentDb.Name.

Now, use the BeginTrans method of the new workspace object to begin a
transaction, and CommitTrans to commit it, or Rollback to roll it back.
You'll usually put an On Error Goto <label> statement right after the
BeginTrans that jumps to a location that performs a Rollback, then resumes to
the line that begins clean-up and exit, subsequent to where the CommitTrans
would have occurred.

On Wed, 29 Dec 2004 23:52:27 -0000, "Raposa Velha" <jm******@hotmail.com>
wrote:
So, chosen the interface for my app, finally I got stuck with transactions.
Boy!, how many ways of doing the same job are there?! Any suggestion so I
could find myself in the right direction? Pasting some code would be
appreciated.

My goal is updating several tables and rollback if any of the updates fail.
Not much, hmm? I've seen in this NG may discussions (I even gave a try on
the MS Access help, what a loss of time!!!) but couldn't decide between
which technique.

Cheers,
RV


Nov 13 '05 #3

P: n/a
"Allen Browne" <Al*********@SeeSig.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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #4

P: n/a
On Thu, 30 Dec 2004 23:00:45 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"Allen Browne" <Al*********@SeeSig.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>.<fieldname> 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.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.