Multi-user locking

[Guest]

I have read a collection of posts about locking, but continue to have
problems. We have a FE/BE with ACCESS 2003 and have addressed the locking
settings recommended and the correct version of JET. Most machines are W2000
with a few WXP with a W2003 Server. There are 14 users. On deadline days
especially, when 6 or more users are editing in the same form they experience
slow-down, locking problems and ocassionally corruption. Currently, the
database opens to a main menu that is based on a single line table (in the
BE) containing the current sale date. From here, other forms can be opened
to edit contracts, add contracts, and a great many other things. The
contracts are long and it takes time to edit them. Currently, when a user
opens the Edit Contract Form, the form is filtered based on the current sale
date. These problems have worsened since converting from A97. So, questions:
1. I have read some mention of not basing forms on queries or tables. Would
this be worth trying for these long edits?
2. Does filtering records on a query beneath a form impact record locking in
any way?
3. There are actually 2 BEs, one with 5 tables shared by other databases
inculding 1 SQL table, and the other containing 25 tables exclusively for
this database - could this cause problems?

4. Any other suggestions for solving these locking and slow-down problems?

One final thing, if someone finishes editing and returns to the main menu
then goes to lunch, they are sure to lock other users who cannot proceed
until someone closes the database on that machine.

Thanks,
Amanda

 

[Larry Linson]

I have read a collection of posts about
locking, but continue to have problems.
We have a FE/BE with ACCESS 2003
and have addressed the locking settings
recommended and the correct version of
JET.

I am hoping that, as is repeatedly advised here in the newsgroup and
elsewhere, you have put a copy of the Front End on each user's machine.
Having multiple users logged in to the same Front End or monolithic database
greatly increases your chance of corruption.

Most machines are W2000
with a few WXP with a W2003 Server.
There are 14 users. On deadline days
especially, when 6 or more users are
editing in the same form they experience
slow-down, locking problems and
ocassionally corruption.

Your somewhat complex back-end arrangement could have an effect, possibly,
but on a 100 MBPS LAN, even not-so-good Access applications have supported
14 concurrent users. I was able to do that, and more, 'way back with 16-bit
Access 2.0.

. . . Currently, the
database opens to a main menu that is
based on a single line table (in the BE)
containing the current sale date. From here,
other forms can be opened to edit contracts,
add contracts, and a great many other things.
The contracts are long and it takes time to
edit them. Currently, when a user opens
the Edit Contract Form, the form is filtered
based on the current sale date.

These problems have worsened since
converting from A97.

I am not surprised, particularly if you have not applied all three Service
Packs to Access 2000 and all eight SPs to Jet 4.0. And, I am astonished at
how many shops have not done so.

So, questions:

1. I have read some mention of not basing
forms on queries or tables. Would
this be worth trying for these long edits?

I am not inclined to use unbound forms which I gather is what you are
talking about. You are likely to encounter more data problems. That would
be, in my view, a drastic last resort and may well not provide any benefit.

2. Does filtering records on a query beneath
a form impact record locking in any way?

My strong preference is to not use "filtering" but to use a Query with a
Criteria to limit the form to just one record (or the page in which that
record resides, if using page locking). I believe that using a "broader"
query and then applying a filter may read more records, and, it may lock all
the records read. In any case, replacing the Form's Record Source with a
one-record Query will be optimum.

3. There are actually 2 BEs, one with 5 tables
shared by other databases inculding 1 SQL table,
and the other containing 25 tables exclusively for
this database - could this cause problems?

I don't know. Perhaps someone else has experience sharing back-ends between
database applications and can chime in here. I've used Access to create
client apps for server databases, but not had any hybrid shared Jet
multiuser-client to server applications.

4. Any other suggestions for solving these
locking and slow-down problems?

One final thing, if someone finishes editing and
returns to the main menu then goes to lunch,
they are sure to lock other users who cannot
proceed until someone closes the database
on that machine.

Clearly, the first place to look for what is causing a locking problem is
here... because it is reproducible. From your other discussion, this form
could well be the major culprit. If it is just for selecting the particular
record to be edited, make it read-only, and set no locks. If it allows
updates, maybe it should not. And, maybe you should rethink your application
interface so that you do not use a "list of everything".

If you are not using record-level-locking, I suggest you give it a try. If
you do that, and take a look at the logic of your application to ensure that
you don't have two users working on the same record at the same time, that
may help.

If you have lookup tables, relatively infrequently changed, in the back end
or on the server DB, you might move those to the Front End and see _some_
performance improvement.

There's an introductory presentation on Access in a Multiuser Environment
that I did for my user group that you can download from
http://appdevissues.tripod.com. It will identify topics that I thought
worthwhile to discuss, and a bit more. I'd guess you are 'way past needing
that level, but I suppose it couldn't hurt to take a look.

The best collection of detailed information and links on the subject of
Access in the multiuser environment is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

 

[Guest]

We have the same problem, we have discovered that we lock up when someone
leaves their computer while entering data and leaves the cursur in pencil
mode.

 

[Van T. Dinh]

1. Check Tools / Options ... / Advanced tab / Default Record Locking pane
and "Open Databases using Record-level locking" option.

2. If the user enters / edit data through a Form, check the Record Locks
Property of the Form.

 

[Guest]

Larry,
Thank you for the suggestions. We do broadcast the FE to each computer upon
startup, so everyone has a copy.
I'll try a few things to address the locking problem on the main form. This
is a problem that has emerged since transitioning to Access 2003. I hadn't
been looking at it as an opportunity - thanks for pointing that out.

Querying one record for editing is very unpopular in the office, since
people have become used to using the <Ctrl '> to copy a field from the
previous record. However, we may try it to see if it helps.

Amanda

 

[Guest]

We have this problem while editing contracts - a long process often
interrupted by phone calls. We've tried to train around this issue.
However, the single field on the main menu is almost never edited, so I don't
think it explains what we're seeing there.

 

[Van T. Dinh]

Even if the Field value is not edited, each user's Main Form will try to
lock the Record (in the single row Table) by default.

Check the Recordset Type and Record Locks Property of the Main Form.

 

[Larry Linson]

"Amanda Woodward" wrote

Querying one record for editing is very
unpopular in the office, since people
have become used to using the <Ctrl '>
to copy a field from the previous record.
However, we may try it to see if it helps.

If this is data entry and they are <Ctrl'>ing from the just-entered record,
you could save all the entries in Form-level variables, and pre-populate the
pertinent controls in next new record (in the OnCurrent event).

Larry Linson
Microsoft Access MVP

 

[Guest]

The default record locking is set to No locks. The 'Open database using
record-level locking' option is checked.
We could move this main menu single-record table to the front end, so each
user enters and edits their own date table. Then do the hidden connection to
the BE that Tony's article suggests.

Amanda

 

[Guest]

Thanks for this idea - In fact, sometimes users would like to copy a few
fields from a contract that is not the previous record. Perhaps we could
program an F key to copy fields from a contract of their choice.
Amanda

 

[Guest]

The recordset type is a dynaset, the form record locks is set to No Locks.