[AccessD] Lock of Record - Memofields

Tortise tortise at paradise.net.nz
Sun Jul 15 21:20:50 CDT 2007


I'd love to know the answer to this one too!

We have an Access 2003 database which does much the same it seems, no SQL backend, problem is intermittent and is almost certainly 
associated with one memo field which locks after a small number of saves are made in it.  The other fields save fine.  The kludge we 
used was to add a second memo onto the form and save subsequent edits to that, which works reliably!!!

I posted a long time ago about this but no one came up with a solution that I could work.

Kind regards
David Hingston
----- Original Message ----- 
From: <pcs at azizaz.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Monday, July 16, 2007 2:06 PM
Subject: [AccessD] Lock of Record - Memofields


Hi,

I have a problem with record locking and would like some
comments on this. This might be a bit of a long winded
explanation so bear with me....

This is the setup:

Access2003 .mdb with SQL Server2005 Backend.

Access .mdb is set to 'no record locks' and so are all Forms.

Around 60 Users sitting in the application all day using
Terminal Server.

All tables odbc linked.

There's a main Summary Form with a number of pre-defined
filters.

The Summary Form is populated using ADO recordset.

>From Summary Form User clicks on a record line to access the
Detail Form.

The Detail Form consists of of a subform control that
dynamically loads one of four different subforms. The main
form is unbound. Each of the Subforms uses the same record
from the same table as source.
On the main Form there are four different labels -
the click event here loads a particular subform into the
subform control.
The subform is populated using an ADO recordset call.

On the Main Form there are two command buttons:
Enter Edit Mode  (setting the allow edits property of the
Form to Yes)
Exit Edit Mode (setting the allow edits property of the Form
to No)

The subform is always opened in "View Mode", i.e. the allow
edits property of the SubForm is set to No.

I am using the following record locking scheme :

When User is clicking on Enter Edit Mode, code checks the
record in question - using ADO call - to see if two fields
on the record: "LockedBy" and "LockedDateTime" are null.
If they are null, the ADO code updates the two fields with
UserName and Current Date Time, and calls the record in
again from the table to display on the SubForm currently
selected and sets the 'allow edits' to true on
the SubForm.

The Exit Edit Mode sets the two fields to null and saves the
record doing a straight docmd.save.

This Exit Edit Mode code is fired before User is leaving the
record/form or executing other code than runs an update
query on the same record.

However, we are encountering the situation where User when
exiting the Edit Mode gets the *Access* Lock Message, where
User can either drop changes or save changes. User has been
instructed to drop changes, which causes the record on the
Form to be set back to View Mode with the LockedBy and
LockedDateTime Fields still set, resulting in User
now 'locking' himself out of the homemade locking scheme.

I am pulling out my hair trying to figure out what is
causing the built in *Access* Lock Message. I have been over
the code in detail making sure that User is not able to
create changes to the record while in Edit mode other than
via the Form. Other Users can obviously not access the same
record via the Form in Edit mode and there is no code in the
user interface that runs an update query on the same record.

So what is causing the *Access* Lock messages?

I searched the AccessD Archives the other day and I suspect
the cause might be Memo Fields.
If I understand what I read in the AccessD archives correct:
Access on records with MemoFields are stil applying page
locks and not record locks?

There are five memo fields on the record of the table in
question.

Thank you for reading this far!

What's your take on this?

Is it the memo fields that are causing this??

What can I do to remedy this situation?

The memo fields are basically comments fields.

Is this a way to go: Place each comments in separate table
in a record with a 255 character field - one to many
relationship with main table (and include a comments type
field), and on retrieving the main record build up the
comments from the associated records into one unbound text
control??

Currently the Comments memofield is popuplated and looks
like this:
2007-05-23 12:35 jcitizen: Comments Text (vbCrLf)
2007-05-22 15:40 bbrown: Another Comments Text (vbCrLf)

in descending date order

Regards

borge
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list