Data able as ueue with several concurrent entries

[Lorderebus]

Hi.

I'm in dire need of help!

I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.

The tables are organized somewhat like this

tblQUEUE
PK_ID_Job(AUTO_NUMBER)| JOB_NUMBER(INT)|LOCKED(BOOL)

tblWorkOrders (FK_ID_JOB (INDEXED DUPLICATES ALLOWED))
FK_ID_Job(LONG)|Name(STR)|Date(DATE)|

Now it works like this, the user gets an ID_JOB from queue
in the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.

Regarding the users, the database is split, multi-user, with >30 simultaneously

i'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.

does anyone has a better idea how to do this?

 

[StarGrabber]

Why do you want to update the locked? Let the users do this when they open their form (or start working on the job). Then they should run an update query which sets the value of the field "LOCKED" to 'True'.

 

[Lorderebus]

That was my original plan, but due to having several users it often happens that they get the same work order because they pressed the "next" button at approximately the same time.

In about 100 entries 10 users got the same work order, possibly due to the query running delay, not sure why, but it happens.

 

[StarGrabber]

What kind of backend do you use? An accdb (or mdb) file?

 

[Lorderebus]

I'm using Accdb with MSO 2007.

 

[StarGrabber]

That's what I thought. - Having >30 users you should consider to migrate to the (free) MS SQL Server Express edition.

 

[Lorderebus]

Would if i could, but unfortunately that's not an option, not only for the monetary reasons, but also because it simply wouldn't be allowed. It's one of those cases that if life gives you lemons you might as well do lemonade cause you sure as hell ain't getting anything else to drink

 

[StarGrabber]

Unfortunate! - Hope that other forum members can help you with their ideas.

 

[Lorderebus]

Ty, for trying to help