Check for a locked record

[arnelgp]

i recommend the Default, No Locks.
Access will Lock the record when you are about to save it.

 

[The_Doc_Man]

The most common - but not the easiest - method to ASSURE no problems is a two-step solution.

Part 1 - every user has a unique ID. Every record to be edited has a slot for "active editor". You create a button that when you are on a record, you can assign it to yourself with that button. You attempt to update the active editor field. The code checks for the slot to be 0 and refuses to do so if not. In that case, it tells you who actually has the record at the moment.

After that attempt, VERIFY that you actually got it (i.e. your ID is now in the "active editor" slot), since two people could try to take it at the same time, but only one can ever get it. Once you assign the record to yourself, your form will not allow you to abandon the record by navigation until you release it (set the "active editor" field to 0) or update it (update the record INCLUDING setting the active editor field to 0). A "bells and whistles" touch would be to set a timer on the record so that if you abandon it, you get an automatic reset. If you close the form, the Unload code would also trigger a release of the record (or would cancel the unload operation). This DOES assume that you have your app "locked down" pretty tight. For what it is worth, this is more or less the method used by help desks, perhaps differing in fine details but exactly what is done in general.

Part 2 - then let Access have a "No Locks" or "Optimistic Locking" style for updating that record. Then with no locks, there can be no interference. With optimistic locks, there IS still a lock but it lasts only for as long as it takes for the update to occur. I.e. a lock-update-unlock sequence that happens almost as though it were a single transaction, though it isn't really.

 

[The_Doc_Man]

I don't have the chutzpa to think I can handle it better

And I am the same as Pat here. The lock-update-unlock (Optimistic Lock) sequence is done behind-the-scenes in the Access update code, which is compiled. Anything I tried to write would be VBA, which is interpretive and much slower. Not to mention that we would need to know exactly what the Update does in fine detail in order to know how to handle potential failures. But Access is not OpenSource so we CAN'T know the fine details.

The "reserve the record" method fixes the problem the easier way... by preventing the need to fix a conflict because if you correctly reserve the record - and HONOR that reservation - you can't develop a lock conflict anyway. What's that old saying about "an ounce of prevention..."?

 

[gemma-the-husky]

Having no locks doesn't mean it's a wild west free for all.

This all works on the basis that you can have multiple users reading a record. The problem occurs when someone writes a record.

What no locks (optimistic locking) means is that access assumes that a write will be successful, but re-reads the record to see if it changed while you were editing it. If it didn't change then your write can proceed. If it did change then you get the "another user edited etc... ", and you have to start again. Often you yourself might be the culprit, if you have two similar forms open simultaneously.

Now if you use a pessimistic locking scheme, you have the difficulty of coming up with a way to lock a record when you need a lock, release the lock, and especially release a lock when you get an operational problem/conflict, which is all decidedly non trivial. Best used only in very specialized circumstances. (Such as a financial transaction moving funds between accounts, where all steps have to complete correctly)

 

[Oreynolds]

I've developed hundreds of Access applications (and dozens more mainframe applications) and none have ever had contention problems. Think about it. What causes a user to go to a specific record to update it? Some interaction with a customer. Is the customer going to be on the phone with two users at the same time? I doubt it.

One situation that does arise which can cause contention is a task list. In this case, I use a technique much like what Doc suggested. The task list is displayed including a field with a user name to show who is working on an item. When a user picks a task to work on, the form updates the record with his ID. Then if someone views the record, they can see who is currently working on it but they cannot update it.

To make this work, you also need a feature that lets a manager unlock a locked record.

AND unless I am implementing a task list, I allow Access to handle record locking. I don't have the chutzpa to think I can handle it better As you have discovered "Edited Record" extends to a block and will cause false positives. Always use optimistic locking. It is the minimum intrusion.

Thanks for your reply Pat. Whilst I take your point on the potential for record locking, in my organisation accessing same records simultaneously does happen regularly. For example part of the DB is detailed information and procedures for technical systems on sites (Site Records). So DB users can be accessing it to obtain information they might need to generate quotes, whilst another user is updating it whilst they process service records, at the same time an engineer may be ringing in to ask a user to look a certain piece of info up. It may seem unlikely but across our DB it does happen daily.

The other time it happens is when adding new quotation records to the system. At any one time at least 2/3 estimators are adding new quotes, amending existing ones etc etc simultaneously. One instance I have narrowed down is when two estimators add a new quote within minutes of each other and both have their respective records open. The block locking makes total sense in this example as almost every time, if the first user to add a quote comes out of his whilst the other person is still in the one they created and then user 1 goes back into theirs its locked, even though no one is in it. Now its obvious that both these quotes that were created within minutes of each other and are sequential in the Quotations table are likely to be in the same disk space block, hence the first is locked by the second. If you request the other user exits their quote then your quote frees up, and vice versa. Very frustrating and happens daily.

Currently I have 'Edited Record' lock turned on on each of my forms but reading this thread and the response from arnelgp it seems that the advice it to turn these locks off altogether. That said in the case of my DB that will result in user clashes with the second most user who accessed a record losing their changes?

 

[gemma-the-husky]

Thanks for your reply Pat. Whilst I take your point on the potential for record locking, in my organisation accessing same records simultaneously does happen regularly. For example part of the DB is detailed information and procedures for technical systems on sites (Site Records). So DB users can be accessing it to obtain information they might need to generate quotes, whilst another user is updating it whilst they process service records, at the same time an engineer may be ringing in to ask a user to look a certain piece of info up. It may seem unlikely but across our DB it does happen daily.

The other time it happens is when adding new quotation records to the system. At any one time at least 2/3 estimators are adding new quotes, amending existing ones etc etc simultaneously. One instance I have narrowed down is when two estimators add a new quote within minutes of each other and both have their respective records open. The block locking makes total sense in this example as almost every time, if the first user to add a quote comes out of his whilst the other person is still in the one they created and then user 1 goes back into theirs its locked, even though no one is in it. Now its obvious that both these quotes that were created within minutes of each other and are sequential in the Quotations table are likely to be in the same disk space block, hence the first is locked by the second. If you request the other user exits their quote then your quote frees up, and vice versa. Very frustrating and happens daily.

Currently I have 'Edited Record' lock turned on on each of my forms but reading this thread and the response from arnelgp it seems that the advice it to turn these locks off altogether. That said in the case of my DB that will result in user clashes with the second most user who accessed a record losing their changes?

Yes, but your activities don't necessarily cause problems. Estimators entering different orders/quotes isn't an issue, as the optimistic process doesn't lock a block of records, or indeed any records.. It just re-reads the single record to see if a different user changed it while you were doing your own edit. (I believe the database manager process - ie access - does lock the reread for an infinitesimal duration, but that's nothing to worry about. It actually tries multiple times in case there really is an ongoing update, to allow the other micro lock to release)

People just viewing records is never an issue, unless you are logging the views somehow, which would involve writes.

If you don't lock records there's no issue at all.

Multiple users editing the same record might be an issue whatever you do. It's worth reviewing your workflow to understand how this happens.

The problem might be a normalisation issue, having data in a table that really should be in a sub table.

The write process is actually instantaneous (virtually). The circumstances when you get concurrent updates is vanishingly small..

 

[Oreynolds]

I'm having trouble visualizing a department where multiple users would be updating the same quote simultaneously. That sounds like chaos to me, but whatever.

The problem you are experiencing is caused because you have chosen the Locked Record. ALL recommendations have been to change to no locks. That could simply eliminate the problem entirely if it is always being caused by adjacent records being locked. AND, when you are adding multiple quotes at the same time, they WILL BE ADJACENT. Because new records are added immediately following existing records and so will frequently be in the same.

If you actually have people changing the exact record at the same time, I am pretty surprised but you can control it as Doc and I suggested. OR, you can show the RecordSelector and train the users to pay attention. If they open a record and someone is editing it, the circle will show.

OK, I will give it a try removing the locks.

To be clear we do not have multiple people editing the same quote record simultaneously. We do potentially have people viewing information records simultaneously.

The main problem occurs with adjacent records. At peak times there could easily be 4 people adding new quote records and building the quotes and obviously these end up being adjacent records. So if 4 people all add a new quote record in a 10 minute time period and each person spends up to 10-20 mins in their respective quotes then this is when adjacent new locked records seem to affect each other. As soon as you ask predominately the last user that added a quote record to exit their quote it tends to unlock others that may be affected. It happens daily and the users have got used to it and how to get out of it but it's very annoying. It happens with purchase orders too.We have several project managers working simultaneously often creating PO's for the respective jobs which ultimately end up as adjacent records. Again one record, often the last to be created will often lock a couple of other records above it in the table which then affects other users.

I'll give it a go removing the locks. My only concern is I did accidentally leave the lock off on a form some time ago and we ended up with more than one user accessing a record, for example an accounts user entering the record to update an invoice detail whilst a PM was logging in to update some notes on the job. Whoever accessed 2nd gets no warning until they have toys in their data at which point they get told it's locked and lose any work they did. You might think the chances of this happening are slim but with multiple departments all doing their respective jobs relating back to large long term project records then in reality it does happen more than you would expect.

 

[gemma-the-husky]

It goes back to consideration of your processes.

When you say "update an invoice details", or "update notes on a job".

Well I imagine the notes should be in a notes table, not in the jobs table. The invoice details should be in an invoice details table not in the job table

If you normalise properly you won't get clashes, as adding an invoice detail, and adding a note affects different tables, so no locked records.

You don't need to lock the job to modify a note linked to the job. You shouldn't need to update the job itself very often at all, so you are unlikely to have an update clash when updating a job.

Can you show us the fields you have in the jobs table?

 

[Isaac]

It goes back to consideration of your processes.

When you say "update an invoice details", or "update notes on a job".

Well I imagine the notes should be in a notes table, not in the jobs table. The invoice details should be in an invoice details table not in the job table

If you normalise properly you won't get clashes, as adding an invoice detail, and adding a note affects different tables, so no locked records.

You don't need to lock the job to modify a note linked to the job. You shouldn't need to update the job itself very often at all, so you are unlikely to have an update clash when updating a job.

Can you show us the fields you have in the jobs table?

Excellent point