[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