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