Using Transactions in Access 2007

[dweeber62]

The temporary table concept sounds promising. The project manager is
only insisting that the interface require the user to click a "Save"
button to make any data entry part of the database. I felt like this
requirement was a step backward database design, but his reasoning has
merit.

I'm thinking about the temporary table idea. It seems that syncing the
backend table with the front end temp table would be very similar to
using transactions to update the data.

 

[Arvin Meyer [MVP]]

Yes it is. Very similar. The difference being that the way that you want to
use transactions requires the form to be unbound to the data. So first you'd
have to fetch the row that you wanted to edit, then you'd need to write it
back. As Tom mentioned, you cannot put a lock on the record while you are
doing this, so you could be overwriting another user's changes. Using a
temporary table, you get a chance to see the record that you would be
overwriting.

 

[dweeber62]

Thanks for your comments. I really want to understand the process, and
right now it's not clear.

I understand that after user 1 gets a record, user 2 might get the
same record. Depending on what each user does, there's a likely chance
that either user's changes will be lost.(The problem being there is no
record locking.)

How does a temporary table improve the situation? Both users can still
retrieve the same record. I could compare the temporary table to the
original table to see if any changes were made, as part of an
updating/saving process. I would catch the problem, but to resolve it
without losing either users' changes seems to be very complicated.

Yes it is. Very similar. The difference being that the way that you want to
use transactions requires the form to be unbound to the data. So first you'd
have to fetch the row that you wanted to edit, then you'd need to write it
back. As Tom mentioned, you cannot put a lock on the record while you are
doing this, so you could be overwriting another user's changes. Using a
temporary table, you get a chance to see the record that you would be
overwriting.

Hidi Ho,
The Dweeber

 

[Arvin Meyer [MVP]]

However, if you use Access's native design mode, which is bound records,
then there is record locking and the entire problem goes away. You can
simply create a new record and discard it if you change your mind, or open
and lock an existing bound record, and discard any changes should you decide
that you don't want the edit. No harm no foul.

The only advantage of a disconnected record is speed if there are a huge
amount of records (more than several hundred thousand), lots of users, more
than 20 or 30 active concurrent users, or a WAN, such as the Internet. In
all of those cases, there are other , usually more appropriate data engines.

 

[dweeber62]

It sounds like the best method would be to take advantage of Access's
native record locking (and using bound forms). I could accommodate the
manager's insistence on having an explicit "Save" command by using the
form's OnDirty, BeforeInsert and BeforeUpdate properties to keep users
aware of the status of their data entry, and to require user
conformation before committing any changes or additions.

Thank you for your comments. They were very helpful.

However, if you use Access's native design mode, which is bound records,
then there is record locking and the entire problem goes away. You can
simply create a new record and discard it if you change your mind, or open
and lock an existing bound record, and discard any changes should you decide
that you don't want the edit. No harm no foul.

The only advantage of a disconnected record is speed if there are a huge
amount of records (more than several hundred thousand), lots of users, more
than 20 or 30 active concurrent users, or a WAN, such as the Internet. In
all of those cases, there are other , usually more appropriate data engines.

Hidi Ho,
The Dweeber

 

[David W. Fenton]

Pessimistic locking (edited record) is certainly the favoured
strategy these days. Optimistic locking (no locks) was only
favoured in the past because of Access's lack of support for true
record locking then; it locked the page, so adjacent records were
unnecessarily locked.

Does anybody use record-level locking? Truly? I certainly don't.
Optimistic locking is just fine, in my opinion.

 

[David W. Fenton]

Why waste user's time by unnecessarily allowing them to edit a row
which another user has in the meantime edited? It rarely makes
sense.

If you have an app where it happens anything more than very rarely,
I'd suggest you might be better off with a different back end.

Thus, you handle when it happens, rather than pre-emptively
overtaxing the record locking engine.

I'm not certain about this, but I've heard that the way Jet actually
implements row-level locking is by storing each record in a single
data page. This means your database will have lots of extra space in
it, though with today's large hard drives, that oughtn't really
matter. It does mean that operating on large sets of records will
have to retrieve more raw data across the wire, though, again, I
doubt that matters much these days.

Far more
authoritative voices than mine have said the same. Here's what
Litwin, Getz and Gunderloy said on the subject in the 2002 ADH:

"With Access 2002's support for true record locking, however, we
now believe

They "believe" -- they don't prove it. They say lots of things that
turn out to make no sense (such as the chapters on replication in
the A97 and A2000 versions of their book, and their idiotic
comparison of DBEngine(0)(0) and CurrentDB()).

that pessimistic locking is the preferred choice for most
applications...."

That's their opinion. I've never implemented it in a single one of
my apps and have not encountered issues at all.

If your working environment makes optimistic locking the preferred
strategy, then use it, but my experience firmly puts me in the
camp of the holy trinity of LG & G as regards the generality of
the issue.

I would say use optimistic locking by default, and choose
pessimistic when optimistic locking (and handling the collisions)
becomes a problem. And at that point, I'd be looking at upsizing, to
be honest.