The data has changed message

[Jimbo]

Ever since I moved the access 97 backend to a SQL Server backend with
Access 97 front end..some forms pop up with a The data has been
changed...Another user edited this record and saved the changes before
you attempted to save your changes. Re-edit the record. Message...when
I begin to type in the fields...then it allows me to type and save..is
there a way to block this message?

[Johnny Meredith]

I think that's a concurrency exception. I would read up on this,
because it may change the logic in you app (what really should happen
if a record that I have cached locally changes in the back end? Do you
want a "Last-In Wins" rule, or something more complicated?)

You could also try pessimistic locking, which will block access to a
record that's currently being edited, so this error should never occur.
Downside is, if a user starts to edit a record on Friday at 4:59 PM
and leaves the record in edit mode all weekend, no one else will have
access to it until it's released on, presumably, Monday.

Someone correct me if I'm wrong, but I think you have to handle
concurrency manually in Access if you want to use optimistic locking?
I know in .NET, you can download Microsoft's data access application
block (DAAB) which has a (sort of) built-in concurrency handling
mechanism (for anything other than a last-in wins approach.)

Thanks,
Johnny

[Trevor Best]

Jimbo wrote:

Ever since I moved the access 97 backend to a SQL Server backend with
Access 97 front end..some forms pop up with a The data has been
changed...Another user edited this record and saved the changes before
you attempted to save your changes. Re-edit the record. Message...when
I begin to type in the fields...then it allows me to type and save..is
there a way to block this message?

Not block, but prevent! :-)

Adding a timestamp column to the SQL Server table may help here. The
problem can arise from floating point numbers used for storage (datetime
is stored as floating point) and there are invariably rounding errors
due to the fact that floating point numbers in IEEE format cannot
represent every fraction properly (rather like the problems in having
1/3 in decimal, e.g., 1/3 * 3 = 1, 0.333r * 0.333r = 0.999r). Access
will check the value of each column with the original one read at the
start of the record to make sure no-one has changed it since you read
it. With a timestamp column, Access will use only that column to check
for other updates and this is not prone to errors like floating point is.

--
1f u c4n r34d th1s u r34lly
n33d t0 g37 l41d

[Jimbo]

The timestamp did it...thanks!!!