Resolving concurrency issues with bound forms

[Joey]

Hello,

Is there any way to resolve concurrency issues with bound forms in a
multi-user setting? I have a bound form that sometimes spawns a write
conflict dialog box that prompts the user to either save the record,
copy it to the clipboard or drop changes; however, when the user clicks
on the save record it does not work and the record eventually becomes
corrupted with a #error as a value for the concurrent field. I notice
this is particularly the case for bound text boxes in various pages of a
tab control. Is there any way to accomplish this without having to use
unbound forms because the users are particularly fond of the filtering
and searching capabilities of a bound form?

Many thanks in advance,
Joey.

 

[Allen Browne]

Joey, the dialog you describe sounds like the standard Access conflict
dialog. IME, this does not cause corruption, though I always train my users
to cancel their update and respect the other person's since they KNOW their
update did not succeed. Otherwise they are overwriting another person's
update who has no idea their entry has just been clobbered, and I tell the
users they have no right to make such a completely unacceptable and selfish
choice.

Other pointers:
1. Presumably you have already split your database, so each user has their
own separate copy of the front end.

2. You also need to have Name AutoCorrect turned off so Access does not get
confused about which controls is bound to what.

3. It is absolutely essential that all users have the same version of JET 4
(msjet40.dll). Unpatched users will corrupt the database.

4. Anything else peculiar to this problem? E.g. does the corruption only
happen with memo fields?

 

[Joey]

Hi Allen,

1. The database isn't split -- users are using thin clients to access it.

2. I turned off the AutoCorrect on the controls but what is Name
AutoCorrect?

3. All users have the same patches and latest versions of MDAC and JET.

4. Yes, this corruption only seems to happen with memo fields than any
other field.

Thanks,
Joey.

 

[Allen Browne]

1. Thin client? Using Terminal Server or Citrix or similar? Albert Kallal
has some info on how to get Access working reliably over a WAN in this link:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html
It might be worth setting that up so that it is split, and each user gets a
separate instance of the front end.

2. Re Name AutoCorrect, see:
http://allenbrowne.com/bug-03.html
There are well over a dozen bugs associated with this, and it regularly
corrupts the database. (The AutoCorrect for the controls refers to spelling,
and it does not cause corruption, though it is very annoying in combos and
with fields that contain proper names.)

3. Correctly patched. Excellent.

4. Memos: This field type is stored as a pointer. If the pointer is invalid,
the contents of the memo field becomes garbage. You can usually rescue the
other records into a new table with an append query, and even append the
other fields of this one record (i.e. all fields except the memo with the
corrupted pointer).

You might be able to reduce the incidence of corruption by changing some of
the memo fields to Text (255) where appropriate. Also by trying to set the
database up so that any form that contains a memo field is not likely to be
open at the same time as another form that contains the same memo field. But
splitting the database and giving each user their own copy of the front end
is the first thing to try to solve this kind of concurrency issue.

HTH