Couldn’t Update – Currently blocked by another session on this machine (97)

Topic

New Reply

Can someone help me with a fix to this scenario? I have a edit form that accesses one selected record containing 10 fields. One of the fields is a memo field. The form has two command buttons (1. Save record and return to main menu & (2. Save record and print preview report. Everything works fine UNTIL the memo field contains more than approx. 2000 characters. IF you enter the form AND the memo field already contains the 2000+ characters AND you do not edit any of the fields on the form AND you press either of the command buttons; the above Couldn’t Update …. Message Box appears. There is an OK button on the Message Box to close it. There is no way to exit the form UNLESS you go to a field (any field) and do some sort of edit on it; even as insignificant as pressing the space bar at the end of a text field. Then you can exit the form by pressing on either of the command buttons.

Reply | Quote

Replies

Web research suggests you aren’t alone in seeing this kind of issue, even with more recent versions of Access. There is apparently a locking issue that has been identified in cases where the memo field contains more than about 2048 characters – are you using optimistic or pessimistic locking? One solution suggested is to put the memo field in a separate table and link to it when you need to display or edit it. Another suggestion is to move the tables in your database to SQL Server – which seems a bit drastic.

Reply | Quote

I have tried many things to correct this situation, including changing the locking methods. Currently I have had no success. Have also gone to the effort of moving the memo field to an external table and then linking to it on the form. This appeared to work for a short period of time and then for no apparent reason the message box issue returned again. At this juncture I am tempted to use the On Error Resume Next statement. Thank you.

Ron

Reply | Quote

One possible thing you might try to debug the situation is to see whether or not the control for the memo field things it has been changed. You can do this by putting code on the button procedures that save the record and see if either the form or the memo control “is dirty” – you can check that using the IsDirty() function. That may tell you whether your instance has already tried to save the record. If that’s the case, you might put in code to skip the save if the form doesn’t think the record has changed.

Reply | Quote