Multiple user receiving record locked

[KeithG]

Hello,

I have a database we use to enter new account numbers and several other atribbutes about the account and it is split into a FE/BE with the BE in a shared folder. I have the open database using record-level locking checked but it seems to me that the database is opening up with page-level locking. Multiple users will receive a message that the current record is locked and I am sure that they are all not updating the same record. Does anybody have any idea's of what I can do? If nothing else I am going to have to recreate my form as an unbound form and see how that goes.

 

[gemma-the-husky]

even if you have record locking access has to lock the page in which the record resides, so neighbouring records are likely to get locked. you do not often need any locking. access uses optimistic locking which will still report a changed record when writing

 

[KeithG]

Do you have any suggestions for me. How do you achieve no locking? I thought it was either page-level or record-level.

 

[The_Doc_Man]

Look for special locking terms "Pessimistic" and "Optimistic" which govern not the size but the TIMING of lock application. You want to narrow the time window during which locks are asserted.

As to multiple users - if they are adding records, odds are that they would try to add at the same place - the (logical) end of your table. If the table is efficient enough that it multiple records could fit in the same buffer, there is your problem. If another table is being updated "transparently" underneath your form with VBA, it is the "other" table that is getting locked.

 

[KeithG]

If the table is efficient enough that it multiple records could fit in the same buffer, there is your problem.QUOTE]

Are you implying that if multiple records can fit on a single page that is my problem. I have heard of solutions to bloat your records to 4000 bytes so that only one record can fit on a page. Is this what your where getting at?



Thanks for your reply

 

[gemma-the-husky]

for optimistic locking to fail, you need 2 users writing data at exactly the same time (very roughly), otherwise both writes will succeed. HOWEVER if you open a record locked, then presumably a user can stay in a form with a particular record displayed, go to lunch, and the record (and its neighbours) will remain permanently locked, which is much mmore likely to cause locking collisions.

Even if you use optimistic locking, Access will still advise you that a record was changed. Unless its a very special application, I would personally think optimistic locking is sufficient

 

[The_Doc_Man]

KeithG, ... yes, multiple records can fit into a single block-buffer. Yes, ALL such records are locked while the buffer is locked because Access doesn't want to go through the hell of mapping a PART of a buffer to lock a single record. So that was exactly what I was mentioning.

 

[KeithG]

Currently I have my locking options as no locks and open database using record locking. Are you suggesting that I change it from no locks to Edited Records? There are about 15 users in the database strictly doing data entry. Any suggestions are apperciated.

 

[gemma-the-husky]

Personally, I would not open the dbs with record locking

 

[KeithG]

why do you say that?

 

[gemma-the-husky]

because even if you open with no error-locking, you are still using optimisitic locking, which is generally sufficient. I understand that with optimistic locking that when you write data, the dbs manager actually re-reads the record and compares it with your record, and reports to you if the record had changed in the interim, otherwise it commits the write. This is perfectly safe in normal circumstances.

 

[KeithG]

I apperciate your replies. Do you have any suggestions for my situation? I have about 15 users adding a record about every 3 minutes. I have a main form with about six subforms and they usually recieve the locking error when they adding records to one of the subforms. Any suggestions?

 

[rahulgty]

Instead of bound form, best solution I experienced so far, is using Microdoft activeX Data object library, I mean ADODB.connection with un-bound form.

 

[The_Doc_Man]

The only way to handle this with true safety involves an unbound form, using Recordset operations, and having On Error traps in the code around the recordset update.

Your goal is to reduce the size (duration) of the timing window during which the underlying record is locked. If you unbind the form but use recordset ops to populate it under the OnCurrent routine, then build an "Update" button and do the recordset ops under the button's OnClick routine, you can assure the minimum exposure to the locking conflict. But in theory, you can NEVER escape the possibility that your update didn't work. Which is why the OnError trap in the OnClick routine gives you the chance to try to update the record a second time.

 

[KeithG]

Would it be more efficent to add the records through an open recordset or use an SQL statement on an ADO connection to add the records?