Access 2003 - Split Database runtime error 3218

By tiger94n ·
I'm running a split Access 2003 DB with each user having the same frontend installed on their individual PCs with data written via linked tables to a network server. Each user edits/adds only their own data via a form which has the Record Lock Property Set to No. The database frontend and backend are both set as Shared with No Record Locking. Users still encounter runtime error 3218 "couldnot update; currently locked" intermittantly when trying to add a new record. I need a solution.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Collission - retry

by TobiF In reply to Access 2003 - Split Datab ...

To me this sounds as if you get a collission when at least two clients try to update simultaneously.

They don't lock retrieved records, but access to the database for writing may be taken for a few milliseconds.

What if you trap these errors by setting onerror just before the update and reset afterwards and let the error handling routine check for this specific error. Any other error: Pass on to the system as a normal error, but for this one, raise a flag that you check afterwards. If the flag is set, you simply loop back (with a retry counter, just in case...)

Collapse -

3218 Error - Issue Remains

by tiger94n In reply to Collission - retry

Thanks for your response. Let me add to the background as I'm still at a loss as to the cause of the error. Mutiple users open a form based on a query which displays data from a single table filtered based on each user's Network login ID. Once the form opens the next command moves to a new record. To update the form the user completes one data field and selects a radio button or optionally completes no fields and selects a radio button and selects from a combo box. The code which runs as an After Update Event includes the following:

DoCmd.RunCommand acCmdSaveRecord

DoCmd.GoToRecord , , acNewRec

Normally this update occurs very quickly. When it doesn't the error 3218 is displayed.

Because the open form accesses a backend table, the connection with the backend is persistant over the network.

I added the following code based on your suggestion:

Err_Test46_AfterUpdate: ' Error Handling routine
If Err.Number = 3218 Then
Me.RecordLocks = 0 ' Resets record lock
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
LblSave.Visible = True
Me.Text46 = Null
Me.Text46.Visible = False
Resume Exit_Test46_AfterUpdate

MsgBox Err.Description & " " & Err.Number
Resume Exit_Test46_AfterUpdate
End If

As I can't duplicate the error even with two frontends running against the backend data file I'm not sure if the code will solve the issue.

I don't believe the 3218 error is caused by two different users updateing the same record since a user is always working on either a "new" record or is editing their own records previously entered.

Appreciate any further suggestions.

Collapse -

Simultaneous access to common database

by TobiF In reply to 3218 Error - Issue Remain ...

I believe you're a bigger expert in this than I am. So bear with me if I'm wrong.

But I have a feeling that MS Access is, basically, a single-user environment. It still allows to build solutions where several users access the same database, but probably, at the moment of writing a record in a table, this table will be locked for other users. (At least for writing).

In case you use "locking", then you can reserve records or tables for extended periods of time, but the Access engine will still lock the table momentarily just when adding a line.
BTW, how is the record-ID generated?
If you use a counter, then, in order to add a new record, you'd need to first check the index to get the next id in turn. That may also be a couple of milliseconds.

Hmm. I totally different approach. I used it (in a slightly different form) for a macro routine in MS Word in the '90s...

How about if the users would NOT ADD new records?
Pre-populate the database with empty records. They will only have:
RecordID, RecordEmpty as Boolean, and a userid.
Generate n empte records for each username in the system.

And then, convert this so that a user fetches (and maybe even locks) the first of his empty records, and then updates it.

Or convert the background database to some more structured interface with queueing.

Collapse -

Simultaneous access to common database

by tiger94n In reply to Simultaneous access to co ...

The record Id is an auto number field so Access creates each record ID when the Update fires. As the database is split for multiple users, I this is the simplest way to make this work. I've used this approach previously without any lock issues. Since I'm running Jet4 I believe single record locking should occur when the Udate fires, page locking shouldn't be an issue from what I've been able to research.

Collapse -

One more thought about the roots

by TobiF In reply to 3218 Error - Issue Remain ...

I was just thinking how Access would add new record in a multiuser scenario. If you have 20 different engines (computers) just accessing a shared set of files, then you'd try to have some kind of "atomic transaction of reserving" a particular space for this record in the file when you're about to write.

So this error might refer not only to a "logical record" but also to a "more physical piece of file space".

Still, either having just one engine operate the database or using pre-reserved records might help.

Related Discussions

Related Forums