Row level locking with MSAccess 97, VB6, & ADO

Doanair
Doanair used Ask the Experts™
on
I have an application running on multiple computers, processing items from the same database table. Only one computer may be accessing a record at one time. Each record must be opened and remain locked until processing is complete (when processing is complete, the record is updated with new data). How may I grab the next available record without grabbing a record currently in use by another computer/user?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Adjusted points to 100

Commented:
The record will automatically be locked when u are editing it...
Commented:
Are you getting a totally new recordset when you move tot the next record?
When editing a record you can set a flag (in a new field). After updating you can release the flag or give the field another value.

When running your query, you can skip the records that are in use by other users or doesn't have the default value by selecting the ones that have the default value.

Note: You have to use the edit method then set the flag to the record and then the update to release the recordset.
Before the edit method use "SetOption dbLockRetry, 0" (release immediately), otherwise the records will be held for 5 seconds (default).

I used it with an Access database and it works fine, but when using a large database it is a lot of traffic and it will get slower (it works fine upto 15000 records, after that it will get slower).

Succes
HHH

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial