News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, December 30, 2009

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!

Labels: ,


Blogger Terence said...

Very informative!

I ran into the same problem with locking with memo and OLE field.

I would like to ask more about use the "uses ADO to establish record level locking for the first person to open the BE database" approach.

What if I am using the Currentproject.Connection? Is it still possible to force this to use record-level locking?


1:44 PM  
Blogger Alex Dybenko said...

if you are using ADO - then you can set locking option using Connection, see:

3:06 PM  
Blogger Terence said...

I ran into this wierd situation.

I have this simple Access mdb setup:

1. 4 fields, incl number, text only
2. No indexed fields
3. A simple form to show the fields
4. Form and Access' adv option both set to Record-level locking

What is strange is that if I commit editting one record, all the records in the table are locked as revealed by opening the table from Access' interface.

Isnt Record-level locking supposed to locked just *1* record? How come this looks like page-level locking?

I have the db uploaded to:

Could you please have a look and see whats going on?


7:09 AM  
Blogger Alex Dybenko said...

Sorry, going on vacation, not time to look. make sure that you run ADO code to establish record level locking for the first person to open

8:40 AM  
Blogger Terence said...

Thanks Alex, I got it working in the end in a BE/FE setting as suggested by your link that shows how to use an ADO connection to force record-level locking.

Still has 2 questions:

1. While this way to use ADO to force record-level locking works for a BE/FE setup, how to make record-level locking works for a non-split database?

2. I read somewhere that record-level locking in Access (at least for 2003) is actually achieved by blowing up a record to the 4k page lock limit, and this will increase chance of database corruption? Is this true and do you have any bad experience with it?

Thanks again.

1:36 PM  
Blogger Alex Dybenko said...

Hi Terence,
1 - i don't know. But using non-split database in multiuser environment is not a good idea.
2 - cant comment on method, i think you can find more info in internet. I do not have bad experience with record-level locking in Access.

3:48 PM  

Post a Comment

<< Home