Error: 3218 Could not update; currently locked
My client got this error on very simple database, application was split into backend and frontend, BE was located on one of user’s machine (Windows XP) and accessed using network share. Error occurred quite randomly, there was no way to reproduce it. I had no idea who built it, personally I never got such errors in my applications. Fortunately Tom Wickerath helped me to resolve it (check also his article Implementing a Successful Multiuser Access/JET Application):
Are there any tables that include memo, hyperlink, OLE Object, or the new multi-value field (Access 2007 .accdb only)? The reason I ask is that these fields can cause page locking to be invoked, when you might otherwise assume that record locking is being used. For JET databases, I have gotten into the habit of breaking memo fields out to a separate table, with a 1:1 relationship, instead of including the memo field in the same table with other fields. This way, when a user clicks into the memo field on a form, they have immediately committed any changes to the parent record and vice-versa, clicking out of the memo field on the form to any other field commits changes to the memo data. I base this on the following quote from Microsoft "Also, record-level locking is not enabled for Memo data types." Another reason for moving memo fields to their own table is so that I never run into this situation Keep in mind that hyperlink and OLE Object fields involve the same pointer mechanism that memo fields do, so the above discussion applies to these data types equally well.
The other thing that I've been doing in all my released applications for the past couple of years is running code at startup, via an Autoexec macro, that uses ADO to establish record level locking for the first person to open the BE database. Subsequent users will connect to the BE database with the same locking that the initial user establishes. Michael Kaplan points out on his blog site that when a user selects the option to use Record Level locking, that this is only a request, not a demand. So, by using the ADO code in the above article, you are essentially demanding record level locking.
This KB article clearly states this for the new MVF data type in Access 2007 causes page locking.
And yes – main table had 8 memo fields, plus lot of other fields. So I moved all memo fields to a new table, added ADO code to establish record level locking and added code to save record in new table after field update in application form. For several weeks users reports that error gone!
Happy New Year!