Access 2003 Error: 3218 Could not update; currently locked

[stuart]

I have 2 users who ran into a problem with a data entry program
(written in Access 2003). One user was keying into one of the forms
when she got the message "ACCESS Error Number: 3218 Could not update;
currently locked'. About the same time, another user was keying
records into a 2nd form. Even though they were on different forms,
they would have added records to the same table.

The 2nd user had tried to add about 28 records, the first 13
successfully. She said that she did not get any type of error message
that her remaining records were not being added (even though I have
coded an error handler to trap errors). She only realized later that
the last 15 records she keyed were never added to the table.

There are about 3 main users of this application (a split database),
each with their own copy of the front end database, all accessing the
same back end database. There is no default record locking. The
option to "Open databases using record level locking" is checked.

First of all, is there any reason why user #2 would not get an error
message that her records were not being added? On another
application, I've had a similar problem on a single user application
that a particular field happened to be NULL when I attempted to add
the record to the table. Access did not add the record to the table
(which is correct), but it did not give me an error message.

Is this normal Access behavior? If so, any clues on how to handle this
situation?

TIA,

Stuart

[Rich P]

>>
Is this normal Access behavior? If so, any clues on how to handle this
situation?
<<

Yes - normal behavior for any system. Whenever you have multiple users
entering data into the same table at the same time you are going to have
collisions/write conflicts/dead locking...

The way to handle this is to prevent users from entering data into the
same table at the same time. But that is absurd! How would you get
anything done?

Easy - each user enters data into a local temp table. Then they take
turns submitting their data to the main data table on the backend DB.
This would require a flag on the main table when it is in use. For this
flag what you can do is to add a flag table to the backend database.
When a user is ready to submit their data your submit data procedure
would check this flag table to see if anyone is using it - if not then
copy their UserID to this table. When another user wants to submit
his/her data the program first checks this flag table to see if someone
is there first. If yes - pop up a message stating to try again - system
is busy (or something like that). When user 1 has completed submitting
his/her data - at the end of the submit procedure you Delete * from the
flag table. Then the next user can submit data. Then the next user ...
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[stmedlin]

Thanks for your response, but it seems that Access should be able to
handle this. Both users were adding records, and the locking appears
to be only record level, so why is it locking the entire table?

Also, why would the second user attempt to add 14-15 additional
records and still not get an error message? It appears after the
error message, it locked out all attempts by the other user to add any
records.

[Tony Toews [MVP]]

stuart <st***********@ncmail.netwrote:

>The 2nd user had tried to add about 28 records, the first 13
successfully. She said that she did not get any type of error message
that her remaining records were not being added (even though I have
coded an error handler to trap errors). She only realized later that
the last 15 records she keyed were never added to the table.

Does the user add the records by opening a new form, entering data and then close the
form? If so put the following code in the close command button code before the
docmd.close

If me.dirty= true then _
docmd.runcommand accmdsaverecord

This will trigger any messages before the form is closed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

stuart <st***********@ncmail.netwrote:

>I have 2 users who ran into a problem with a data entry program
(written in Access 2003). One user was keying into one of the forms
when she got the message "ACCESS Error Number: 3218 Could not update;
currently locked'. About the same time, another user was keying
records into a 2nd form. Even though they were on different forms,
they would have added records to the same table.

The 2nd user had tried to add about 28 records, the first 13
successfully. She said that she did not get any type of error message
that her remaining records were not being added (even though I have
coded an error handler to trap errors). She only realized later that
the last 15 records she keyed were never added to the table.

There are about 3 main users of this application (a split database),
each with their own copy of the front end database, all accessing the
same back end database. There is no default record locking. The
option to "Open databases using record level locking" is checked.

There are some interesting reasons why record level locking doesn't always engage.
Although I haven't done a lot of research on this topic.

ACC2000: Access Database Does Not Use Record-Level Locking When Started from a
Windows Shortcut
http://support.microsoft.com/kb/238258

ACC2000: Record-Level Locking Does Not Appear to Work
http://support.microsoft.com/kb/225926/en-us

Although these may or may not apply to A2003.

Mind you it's been my experience with Jet 4.0 databases that inserted records
automatically go to their own page. Which is why they can bloat significantly.
Note however that this is subjective observation and I haven't done much objective
testing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[stmedlin]

On Feb 17, 2:52*pm, "Tony Toews [MVP]" <[e-mail]:

stuart <[e-mail]:

The 2nd user had tried to add about 28 records, the first 13
successfully. *She said that she did not get any type of error message
that her remaining records were not being added (even though I have
coded an error handler to trap errors). *She only realized later that
the last 15 records she keyed were never added to the table.

Does the user add the records by opening a new form, entering data and then close the
form? *If so put the following code in the close command button code before the
docmd.close

If me.dirty= true then _
* * *docmd.runcommand accmdsaverecord

This will trigger any messages before the form is closed.

Tony

--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Thanks Tony for your response. To answer your question, the 2 users
are on 2 different forms, but likely would be accessing the same table
to add records. On these 2 forms, the user will key in the data on
the form, hit 'Save', and then enter additional records (without
exiting the form).

[Tony Toews [MVP]]

st******@gmail.com wrote:

>Thanks Tony for your response. To answer your question, the 2 users
are on 2 different forms, but likely would be accessing the same table
to add records. On these 2 forms, the user will key in the data on
the form, hit 'Save', and then enter additional records (without
exiting the form).

Ok, then closing the form isn't causing the problem.

Darn, I was hoping for an easy although obscure fix. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/