How can I temporarily Lock a table?

From: Neil Robinson <vtr_at_firestorm996.freeserve.co.uk>
Date: Sat, 8 May 1999 12:21:16 +0100
Message-ID: <7h16t6$le1$1@news8.svr.pol.co.uk>

I am running an Access 97 front-end over Oracle 7.3 on Unix. Our Order Numbers are stored in a single-record table, which is incremented each time an order number is allocated. This is fine, but because of the code involved in incrementing the OrderNo I have a situation where the following scenario occurs....

User A requests new OrderNo
User B requests new OrderNo
User A receives latest OrderNo
User B receives latest OrderNo (same as User A)

I cannot find a way of incrementing the orderno and grabbing the new orderno on the same command, so I am looking at temporarily locking the table so that User B cannot increment/grab an order no until User A has done so and unlocked the table. The best way for me to do this would be to run an SQL command direct from with Access VBA.

If anyone could let me know the commands for locking & unlocking tables, and also of any potential pitfalls or problems with it, it would be appreciated.

Thanks

--
Neil Robinson
VTR1000 Firestorm Received on Sat May 08 1999 - 06:21:16 CDT