Note that SQL Server doesn't handle the conflicts, but
rather locks the record for that split second when the
data is actually updated in the table (i.e. you save the
record in Access). SQL Server doesn't hold a lock on the
record while the user is sitting there looking at it
(unless you tell it to specifically--bad idea) The
conflict message that you see comes from Access which is
managing a copy of the data for you.
The best way to handle conflicts yourself from within
Access, is to not use a Timestamp (a.k.a. Rowversion)
column, but rather update a datetime column to GETDATE()
every time the record is modified and compare those.
There is no timestamp/rowversion datatype in VBA/Access,
so it is hard to compare them from within Access, making
a true datetime comparison the best way to go.
To handle possible conflicts yourself and circumvent the
message that Access raises, you will have to use an
unbound form and program all of the
updates/inserts/deletes/additions using VBA and ADO.
When a user loads a record to view, the date and time of
the last modification can be stored in a variable or
textbox. If the user updates the record, the date and
time that is now showing in your recordset can be
compared to what you have saved. If it has changed, then
there is a conflict and you can show your own message and
cancel the update.
So it comes down to this:
1) Add a column (LastModified) to your table that holds a
datetime. Set the default to GETDATE()
2) Create an unbound form which will load a record that a
user wants to view/edit and allow for changes to be saved.
3) When the record is loaded on the form, save the
datetime value that is in your LastModified column to a
variable or textbox.
4) If the user saves changes, compare what is currently
in the LastModified column with the value that you saved
when the record was initially loaded. If the values are
the same, update the record (stored procedure is best),
if they are different, notify the user and handle the
conflict.
I don't know what your level of development/programming
experience is, but I hope this bare bones explanation
helps.
Best of luck!
-----Original Message-----
Hi SFAxess,
I know that in optimistic method SQL checks timestamp of the record but
the case is that where is the right place I can check timestamps and issue
my message and cancel updates of the record
programmatically? and how can I
