[AccessD] Record Locking Strategy or Error Trapping

Hi Vlad

You could create a new confirmation table with a one-to-one relation
to tblOrderHeader and with one field only: the foreign key to the ID
of tblOrderHeader.

When the schedulers despatch the order add a record with the order key
to the confirmation table.

To select confirmed orders, use a query with an inner join to the
confirmation table.
To select non-confirmed orders, use a query with an outer join to the
confirmation table where the ID of this is Null.
The field Scheduled may be removed from tblOrderHeader.

/gustav


> We have developed an app for a client that was to be used for only 3-4
> people. Now it's up to 14 and growing. The client does not want to
> migrate to SQL so we need to come up with a viable solution using the
> tools we have available Access XP on a Citrix Network. 
 
> The app is pretty simple, call centre operators at one end of the
> building take orders input the information through unbound
> forms...(Hmmm, lets not get into that discussion) and the
> Schedulers/Dispatchers at the other end, once again Unbound Forms.
 
> Our problem is that the BE keeps getting corrupted and I have a sneaking
> suspicion it's to do with concurrent writes to tblOrderHeader, but can't
> figure out how to trap this. The reason I say tblOrderHeader is because
> it's the only table both Call Centre and Schedulers would write to.
> Basically, the Call Centre staff create orders with the field "Sheduled"
> defaulted to N and when the schedulers despatch the order this field is
> updated to Y. Obviously, this can occur simultaneously.
 
> My question is how best to handle this situation or to trap the error
> when another user is writing to the table and prompt them to try again
> shortly. I have read extensive info on the MSDN site regarding record
> locking strategies and the database is currently set to "No Locks" which
> is basically Optimistic Locking (Tools|Options|Advanced). What impact
> does it have if I change this to Edited Record or All Records. Is there
> a performance hit or gain and what additional headaches does it create?
> Will it all solve my problem? Should I change the Update Retires to 10?
> Or is there another alternative?