trying to minimize Write Conflicts in a multi-user database

On Sat, 16 Jan 2010 23:27:56 -0800, "Paul" wrote:

One approach I've already tried is to have the Timer event of the form save
the form's record every few minutes to prevent against people leaving an
edited record unsaved for long periods of time.

The issue with that approach is that when the user presses the Esc key
to undo their changes, sometimes it won't work. Not
confidence-inspiring. Over hundreds of apps we've never auto-saved on
a timer. However, we *have* had situations where we've forced a save
to the current record before running other update queries on the same
table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Paul wrote:
Based on what you've said, and what I read in that Microsoft article you
referenced, it would probably be a good idea to replace some of my action
queries was DAO code, because I am performing action queries on continuous
(tabular) view subforms.

Sure. It's a good approach for one-off updates. Armen already mentioned
about forcing save prior to performing action queries updating the same
table, which I may do for a bulk operation rather than single record. Of
course, one should be careful on deciding when to force a save - you
certainly wouldn't want to force a save if the record wasn't complete.
Using placeholders or such to nominally satisfy the requirements usually
will leave us worse off. (Not to suggest that anybody would actually do
this; just wanted to be explicit here)

One approach I've already tried is to have the Timer event of the form save
the form's record every few minutes to prevent against people leaving an
edited record unsaved for long periods of time.

As Armen nicely pointed out, that could cause more problems than it
solves. Remember that by default, Access uses optimistic locking so it
wouldn't even be an issue if someone has a dirty record and left for a
long coffee break. That would be a problem with a pessimistic locking,
however! But even if we were using pessimistic locking, I would prefer
that the Timer event cancel the edits (after a warning or so) rather
than attempt to save what could be incomplete or erroneous record.

Thanks for the explanations, Banana.

You're welcome. I'm sure others also helped to illuminate the situation.

Tom Wickerath AOS168b AT comcast DOT net wrote:
Try setting the Recordsource for the form to this SQL statement (or to a
saved query with this SQL statement):

SELECT * FROM table WHERE 1=0

This query is guaranteed to return zero records. Remove the code that you
had in the Form_Load event, which was apparently running the query that
grabbed criteria from the combo box.


Side note. There was a cdma thread on this issue many years
ago where David Fenton posted the results of extensive
performance testing of using anything that boils down to
WHERE False. In my mind, he conclusively demonstrated that
Jet query optimization is not smart enough to recognize that
no records will be retrieved and consequently does a full
table scan.

An alternative that can use indexes to optimize data
retrieval is to compare a unique indexed field to an
impossible value. For example, while only 99.99...%
guaranteed safe,
WHERE {increment autonumber PK field} = 0
will be orders of magnitude faster than WHERE False. On a
large table, this can make a HUGE difference.

--
Marsh
MVP [MS Access]

On Sun, 17 Jan 2010 10:31:55 -0600, Marshall Barton
wrote:

An alternative that can use indexes to optimize data
retrieval is to compare a unique indexed field to an
impossible value. For example, while only 99.99...%
guaranteed safe,
WHERE {increment autonumber PK field} = 0
will be orders of magnitude faster than WHERE False. On a
large table, this can make a HUGE difference.

That's *very* good to know, Marshall... now I'd better go change some code in
a couple of my clients' applications!
--

John W. Vinson [MVP]

On Sun, 17 Jan 2010 13:47:43 -0700, John W. Vinson
wrote:

An alternative that can use indexes to optimize data
retrieval is to compare a unique indexed field to an
impossible value. For example, while only 99.99...%
guaranteed safe,
WHERE {increment autonumber PK field} = 0
will be orders of magnitude faster than WHERE False. On a
large table, this can make a HUGE difference.

That's *very* good to know, Marshall... now I'd better go change some code in
a couple of my clients' applications!

We use Where 1=0 in SQL Server all the time, and it's very fast -
basically instantaneous, even on large tables. The SQL Server
optimizer must be smarter than Access in this case?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

John W. Vinson wrote:

On Sun, 17 Jan 2010 10:31:55 -0600, Marshall Barton
wrote:

An alternative that can use indexes to optimize data
retrieval is to compare a unique indexed field to an
impossible value. For example, while only 99.99...%
guaranteed safe,
WHERE {increment autonumber PK field} = 0
will be orders of magnitude faster than WHERE False. On a
large table, this can make a HUGE difference.

That's *very* good to know, Marshall... now I'd better go change some code in
a couple of my clients' applications!


I am not going to set up the test cases now, but I'm
wondering if using an inner join to an indexed empty table
might be just as fast and guaranteed to work in all cases.

--
Marsh
MVP [MS Access]

On Sun, 17 Jan 2010 14:47:13 -0800, Armen Stein
wrote:

An alternative that can use indexes to optimize data
retrieval is to compare a unique indexed field to an
impossible value. For example, while only 99.99...%
guaranteed safe,
WHERE {increment autonumber PK field} = 0
will be orders of magnitude faster than WHERE False. On a
large table, this can make a HUGE difference.

That's *very* good to know, Marshall... now I'd better go change some code in
a couple of my clients' applications!

We use Where 1=0 in SQL Server all the time, and it's very fast -
basically instantaneous, even on large tables. The SQL Server
optimizer must be smarter than Access in this case?

So do I, usually, and haven't had any real problems. I'm not sure if it's just
that the query is "fast enough" for my tables or if the optimizer sometimes
guesses right!
--

John W. Vinson [MVP]

Armen, Banana,

Ok, I'm persuaded that I should get rid of the saving the record based on
the timer. I started doing it out of desperation over how to solve the
Write Conflict messages. But as I read through the suggestions made by all
of the experts in this thread, I'm optimistic that the problem may go away
when I incorporate those suggestions in my application. In particular:

- Moving memo fields into 1 to 1 tables,
- Loading one record at a time into the main form, instead of loading the
entire recordset and
- Making sure I'm not running action queries on the same record I'm editing
in the form without saving the record beforehand.

Thanks

On Sun, 17 Jan 2010 17:34:17 -0800, "Paul" wrote:

- Moving memo fields into 1 to 1 tables,
- Loading one record at a time into the main form, instead of loading the
entire recordset and
- Making sure I'm not running action queries on the same record I'm editing
in the form without saving the record beforehand.

I've heard the first suggestion many times, but we've never done it
and our apps work just fine.

I certainly recommend the other two. Maybe you could try them first
before making structural changes.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Great suggestion, Armen. Changing the tables is something that will take
days of modification and debugging, so it would require the most time and
effort. I'll try the other two first, and see what results.

Thanks much.

Paul




"Armen Stein" wrote in message
...
On Sun, 17 Jan 2010 17:34:17 -0800, "Paul" wrote:

- Moving memo fields into 1 to 1 tables,
- Loading one record at a time into the main form, instead of loading the
entire recordset and
- Making sure I'm not running action queries on the same record I'm
editing
in the form without saving the record beforehand.

I've heard the first suggestion many times, but we've never done it
and our apps work just fine.

I certainly recommend the other two. Maybe you could try them first
before making structural changes.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com