Preventing two users adding a new duplicate record

[phill86]

Hi

I have an shared application with a front and back end which is designed to book equipment to a room at a date and time specified by the user so the user will be able to create a record as follows

User 1

Item 1 room1 1/1/2011 09:00 - 1/1/2011 12:00

I have some code to check to see if item 1 is booked anywhere else between the time and dates specified above.

So if user 2 has it booked elsewhere as follows it will not allow user 1 to create the new record

User 2
Item 1 room3 1/1/2011 10:00 - 1/1/2011 12:00

So if user 1 tries to create a new booking for item 1 a message will say the equipment is already booked


I have tried to book item 1 on two machines simultaneously by clicking the book item button at the same time on both machines which resulted in a double booking of item 1 which is what i want to avoid.

Record locking will not work in this scenario because I am creating a new record (unless I am missing something )

It is pretty unlikely that two users will book exactly the same item at the exactly the same time but is there anyway i can prevent this from happening?

any help is much appreciated

Regards Phill

[pod]

Maybe you could add an extra step to the booking process; add a confirmation step once the record is created which checks for duplicates, and have it take the appropriate action such as advising the duplicater that his record will be deleted...

[patjones]

Hi Phill,

One way to resolve this might be to use table-level locking so that only one record can go in at a time.

The disadvantage to table-level locking of course is that multiple users won't be able to access the table at the same time, which is a problem if you have a large number of users needing constant access.

Another thing you could try is having Access assign an auto-number primary key to new records, and set the table's design to prohibit duplicate keys. If Access is really adding these records concurrently, it should raise an error preventing one or the other record from going in on account of the primary key constraint.

This is my first post after a taking a little break from the site, so I'm a bit rusty in terms of responding to questions. I hope this gets you started though.

Pat

[phill86]

Hi Thanks for the suggestions unfortunately the table locking is not an option as I will have several users needing access to the table at the same time.

An extra step seems like a good idea but i opted for a random time delay when the book item button is pressed this will make it even more unlikely that two users will book the kit at the same time.



Thanks for your help

Dim MyValue
Dim PauseTime, Start, Finish
Dim TotalTime As Single



Randomize    ' Initialize random-number generator.

MyValue = Int((4 * Rnd) + 1)  ' Generate random value between 1 and 6.
 
 
 Start = Timer    ' Set start time.
    Do While Timer < Start + MyValue
        DoEvents    ' Yield to other processes.
    Loop
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate
    
End Function

[patjones]

Hi,

I agree that it is extraordinarily unlikely that a concurrency issue would arise using this method...but still bear in mind that it could.

Just a quick question...what do you do with TotalTime after calculating it?

Pat

[phill86]

Hi Pat

Not actually using the time to calculate totals just using the time values to see if the item is booked elsewhere between 09:00 and 12:00 for example and if it is that means that it is unavailable to book.

Cheers Phill