Row level locking with MSAccess 97, VB6, & ADO

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DoanairAuthor Commented:
Adjusted points to 100
The record will automatically be locked when u are editing it...
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).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.