[RESOLVED] Question about locking records - vb6 - ADO

Hey All,

I'm writing a simple transaction database for my team using VB6 and ADO. I've never written a db app for multiple people before, so I have a question.

When a user opens a record and a second user tries to open the same record, I want to display a message to the second user that the record is being edited and to try again later.

From the FAQ, I see that both adLockOptimistic and adLockPessimistic will notify me when a second user tries to edit the record, but I'm interpreting it that the error won't occur until I do a command that actually changes a field in the record.

How do I see if a record is opened for editing BEFORE allowing the second user to open it at all? Is there a standard way to do this?

If this isn't clear, let me know. I'll be glad to expand and/or try again with the wording of my question.

Thanks much,
Bret Reece

By the way, if I just missed it in the FAQ or there's a thread that I missed that already deals with my question, please feel free to just point me in the right direction.

Thanks,
Bret

It's not covered in that FAQ article, or any others here.. while questions about that kind of thing are relatively common, there is not a "right" answer, as it depends quite a bit on the circumstances.

Many people don't bother with blocking access to a record, and instead either wait until an "edit" button is pressed before checking, or basically ignore the issue (the last update is saved).


You are right that an error will only occur when editing starts (with adLockPessimistic, or when you .Update if adLockOptimistic was used).

One way to do what you want is to use adLockPessimistic, and as soon as you move to a new record (or when an "edit" button is pressed, etc) do a fake update - edit one of the fields and immediately .CancelUpdate ; if the error occurs the record is being edited, so do whatever you want to do instead. [edit: now I've posted it, I get the feeling this isn't quite right!]

Another option is to add an extra field to the table to say if the record is being edited (either a boolean, or perhaps text/varchar to say the user who is editing) which you write to as soon as your process begins, and check that field when you move to a record.

Thanks very much. I was toying with the fake update idea, maybe in an "IsItLocked" function. I also like the other idea, but I'll probably only tackle that if the first one doesn't go over easily.

Thanks again. I appreciate the help.
Bret