ACTEBS
actebs at actebs.com.au
Thu Jun 5 20:09:30 CDT 2003
Gustav, Good idea I'll give that a go... Vlad -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Friday, 6 June 2003 3:16 AM To: accessd at databaseadvisors.com Subject: Re: [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? _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com