Handling multiple user access causing write conflicts?

[Cavman]

Hi, I'm working on a meeting room booking tool that enables up to 100 people to log in and book meeting rooms into a calendar. The front end is installed onto each of the PC's and these access the back end on the server. It works almost perfectly, but the only issue I have is if 2 people try to book a slot at the same time. It produces a Write Conflict error and gives the option to Drop Changes, Save Changes or Copy to Clipboard. Ideally I'd like to trap this error and display a message alerting the user that someone else is possibly booking the same slot and to try again in a moment (then refresh the display to show any changes made by another user). If someone else has already booked a slot, I don't want the second user to have the option of overiding that booking! I want to stop the error displaying and default it to always drop changes.
Does anyone know a way of doing this? I don't seem to be able to trap the error no matter what I do!
I thought another option may be to somehow lock the recordset, but only lock the records they're editing (which would be limited to the slots on a single day and for a specific meeting room). Obviously I would want to avoid locking the entire table up! Can anyone suggest a solution for dealing with this? I'm nearing my deadline and have run out of ideas!
Thanks​

 

[KenHigg]

Have you experimented with the record locking options?

 

[gemma-the-husky]

how often does it happen that you get the write conflict? how long does the process of booking a room actually take?

it is quite unusual to get conflicts, if the process is realtively short - access actually uses a locking strategy called optimistic locking - which produces the effect you note. any other locking strategy would be harder to implement, and would still give you a similar message/effect

are you sure it is another user, and not yourself - as you can often get this effect by having multiple forms open together, inadvertently

one alternatrive is to set a flag on the room, when a user starts to book it, and test that flag first before allowing anbother user. the problem is making sure the locking flags get released correctly every time.

 

[Cavman]

When I read up on locking options, I came to the conclusion that recordset locking is set on as standard and that's what actually causes the write conflict error! Does that sound correct?

 

[Cavman]

We deliberately set up the situation of 2 users booking at the same time to test the outcome. The error never occurs when a single user is booking, but with 100 people using the database there's a good chance that 2 people will try to book the same room and day at the same time!

 

[KenHigg]

Can you make the error message appear by opening the db on two different machines, etc? If so try changing the locking option. In 2007 I would suggest the edited record locking option and see what happens.

 

[gemma-the-husky]

When I read up on locking options, I came to the conclusion that recordset locking is set on as standard and that's what actually causes the write conflict error! Does that sound correct?

no - the standard locking mechanism is optimistic locking - which is no locking at all. instead the record is re-read before writing a change in case it changed in the meantime - hence the error message you are seeing.

this is rarely a problem to be honest - but may occur in your case if users take a long time to book the rooms they want. as i say, it may also be a coding error - in that your system is causing the error by having two forms/queries open to the same record simulataneously.

say you are looking at rooms, and open a popup form to book a reservation - you may find that the original "rooms" form is dirty (ie has been edited - and this may be the cause of the problem. if so, save any pending edits before opening the reservation form.

 

[Cavman]

Just as a bit of background, the main table consists of a record for each room, date, timeslot and booking name. There are also 'lock' and 'select' flags for each record. A user selects the slots they want by clicking on the 'select' flag, and then clicks a confirm button which opens a recordset for the slots they've selected and cycles through inserting their name in each record. At the same time it sets the 'lock' flag on, which makes the slot unavailable for future user bookings.

 

[Cavman]

That sounds logical Ken. How would I save edits? Sorry, I'm still relatively new to Access! Would I need to put some code to save in the on-click event that opens the reservation form?

 

[Cavman]

Just saw your message above about edited record locking option, Ken. How do I use that? Is it in the options somewhere, or do I set it in the code? BTW I'm using Access 2010

 

[KenHigg]

Not sure about 2010. In 2007 its in Access Options-> Advanced when you click the office icon in the top left corner.

 

[Cavman]

I found the editing record lock option! That seems to have done the trick. I'm no longer getting any errors - it simply stops the record being edited while the other user is accessing it. Thanks ever so much for your help guys!

 

[KenHigg]

Glad you have it working!

 

[gemma-the-husky]

I found the editing record lock option! That seems to have done the trick. I'm no longer getting any errors - it simply stops the record being edited while the other user is accessing it. Thanks ever so much for your help guys!

the biggest problem with record locks is the length of time they last. lets say a user starts to reserve a room, gets called away, and forgets about the room reservation

would that cripple your system. would it stop reports being run, and other users reserving rooms.

its worth testing to see what happens.