Lock a Row in a SQL Server Table from MS Access

[Guest]

MS Access 2000
MS SQL Server 2000

I use code to save data to a linked SQL Server table from a form in MS
Access in a multi-user environment. I create a record set and update it with
the data the user enters in the form. The data is saved when the form is
closed.

The problem is that two users may have the same record open at the same
time. One user changes the data and closes the form. The other user closes
the form next (and the first user’s change was obviously not reflected on the
second user’s form). Now the change of the first user is gone. So the data
from the last one to close the form is saved.

I am using unbound controls exclusively.

I want to open an existing record and check to see if it is locked. If it
is locked, then I can tell the user that. If it is not locked then I want to
lock it. Then I want to unlock the row when the user is done. I want to do
this with code.

Is there some other method besides locking to address this situation?

Thanks.

Wayne

 

[Michel Walsh]

Hi,


Not by locking (you can use some Repeatable Read Transaction, but that is
not really nice for the second user), but by using "oldValue". Bound
controls, in Access, implement that property automatically, since not only
it can help making an undo on the control, but before saving the record,
someone can check if each control oldvalue == field in the table. If not,
someone did change the record since we read it, else, it does not matter,
since the fields to be update are the same as when we read them initially
(other fields may have been updated, but since you did not see them, and
since you don't, don't you, store computed values... why bother about them).


If you are not in VB6, but in Access, you should try to use bound controls.
Access Forms' controls know how to efficiently deal with bound controls.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks for your reply.

I want to avoid bound controls. This is a VB application but I am using the
MS Access program. All the code is written in MS Access Modules and Forms.

I am thinking about this. Put the record in a collection (collection1) when
the form is opened. The user works on the form and finally saves the record.
Before the save I get the original record again and put in in another
collection (collection2). Then I compare collection1 and collection2. If
there is a difference (indicating someone else has changed the record) then
the save routine will be aborted.

What do you think?

Wayne

 

[Guest]

I have found some references that shed more light on this subject:

See Servers\Server Applications\SQL Server\SQL Server Programming and find a
post titled 'Locking a Record' as of 12/13/2005.

Also see page 549 in 'Microsoft Access Developer's Guide to SQL Server' by
Chipman and Baron. They discuss write-after-write conflicts and how to
resolve them.

Thanks.