• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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?
1 Solution
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).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now