Error 3218 Could not update; currently locked. (1 Viewer)

[dealwi8me]

I have a database with 10 users accessing it.
Even though the database is in share mode they get this message "Error 3218 Could not update; currently locked." when they are trying to write a new record.

Any ideas how to fix the problem?

thank you in advace!

 

[Len Boorman]

You need to check the record locking

I assume you are using a Form to add data. On the properties of the form there is Record Locking.

Are you opening your add data form in Data Add mode.

Len

 

[dealwi8me]

The Record Locking is set to "No Locks" but i still have the same problem.

...and as the records getting more the database locks more often.

I don't know what else to try.

If it helps most of the times the database locks when they are trying to insert a value from a particular combo box which is based on a table.

 

[Len Boorman]

The Record Locking is set to "No Locks" but i still have the same problem.

You cannot have a situation where you do not have arecord locking strategy in place except if you display records in Read Only.

So I guess that you have several people attempting to add a record at the same time and what they are actually trying to do is to all wrtite to the same line.

First try changing the No Locks to Edited Record. This may solve problem although if you are using an Autonumber as a primary key you may get an error of primary key duplication attempted.

Len

 

[Saifon]

if you are using an Autonumber as a primary key you may get an error of primary key duplication attempted.

If autonumber is used and you edit the Index property of that field to Yes(Duplicates OK)... would u think this would resolve that problem?!?

Thanks

 

[Len Boorman]

What are you then going to do for a primary key.

You need to think it through very carefully.

When I want to be sure of what is going on I
1) Allow user to select a number (next in series in some way Max +1 or autonumber)
2) Save that record with if necessary default data
3) Recover the record immediately in Edit mode with Record Lock of Edited Record.

L

 

[GinaWhipp]

I have a database with 10 users accessing it.
Even though the database is in share mode they get this message "Error 3218 Could not update; currently locked." when they are trying to write a new record.

Any ideas how to fix the problem?

thank you in advace!

Hmm, I have questions. What do you mean by *Shared Mode*? Is the database split? Is the Backend on the Server and does everyone have their own Frontend?

 

[Cotswold]

Access doesn't lock one record. It locks a block of records up to a specific number of bytes (whose value escapes me) So you if your records are small then many may have a temporary lock flag on them. A user may start editing a record and then simply leave the editing screen open for a period of time, instead of switching the edit off. If they aren't doing that then it surprises me that the lock is a problem as the actual lock is usually brief but you don't say if it a spasmodic event or happening frequently. Switch off all the pointless stuff like autocorrects, etc which cause delays.

( I had one system with hundreds of customers, some with 40+ users and can only recall having a lock error reported a couple of times over 20 years. In those cases I told them to close the open screen and re-open, by which time of course the cause would have gone. I never used specific locking as I couldn't lock individual records as I could with other databases. I just left the lock control to Access )

 

[The_Doc_Man]

@dealwi8me,

Gina asked the same question I would have asked. Is the database split? If you have a monolithic database (tables in the same file as forms, reports, macros, queries, and modules) then it is bad enough, because to be shared, there would be no way to avoid running into file locks.

It is not enough to split the file into front-end and back-end. You must also distribute copies of the front-end to each user and those files must be set up to find the common back-end. All queries, forms, and VBA recordset code must be set for either NoLocks or Optimistic Locking. NEVER use Pessimistic Locking in a shared DB situation. If you are worried about collisions, implement transaction-style processing.

If you do not have this DB distributed & split configuration, then your complaint is nearly automatic. As it grows, the database runs into locks more often. If you have not done so, you need to look up "Splitting a database" on this forum.

@Cotswold,

Despite the option saying that you lock the "edited record," what really gets locked is the disk buffer which for Access is, I believe, 4Kb. This locks the record being edited and any other records in the same buffer.