Data has been changed message

[Guest]

Friday I moved data from an Access 2003 application to SQL Server and am
linking to the DB. My main form for data entry had several subforms. In
testing, I've encountered one problem. When I move to make changed on a
different subform, I get the following warning.

"Data has been changed. Another user edited this record and saved the
changes before you attempted to save your changed. Re-edit the record."

When I click okay, I can go to the next form and all updates are saved in
the end.

I tried to set warnings to false on each form/subform, but that didn't work.

Lastly, when I was moving that data one table had an auto number. There
were three records that had an auto number of something along the lines of
1482948 and we never had that many records. It seemed that this table was
somehow corrupt. My work-around was to extact for just a given period which
was outside the corrupted date range, but any ideas on this as well

Thank you!
Sash

 

[Guest]

I did a me.refresh on the exit of each form and this seemed to work. Does
that sound right? I'd still love to know if anyone can give me advice on the
corrupted data.

 

[Stefan Hoffmann]

hi Sash,

I did a me.refresh on the exit of each form and this seemed to work. Does
that sound right? I'd still love to know if anyone can give me advice on the
corrupted data.

This message appears, when another user or process has changed the data.

Before saving the dataset Access/Jet looks for the dataset and checks,
if it was changed. It uses the following mechanism:

Table with fields on server:
ID, A, B, ..., Z
ID is the primary key.

Access queries the server for:

SELECT ID, A, B, ..., Z
FROM Table
WHERE ID= AND A= AND B= AND ... AND Z=

It tries to reload the record by comparing _all_ fields of the dataset
with the data on the server, even if it knows the primary key. When the
data has not changed, it returns one record.

Contains the table a field with an unprecise data type, like FLOAT, then
this query can fail due to a rounding error, because Access/Jet uses a
slightly different rounding than SQL Server. When the record count
returned by this query is 0 then the message appears.

Solution:
Don't use unprecise data types in your primary key fields.
Add a TIMESTAMP field in your table. Then Access/Jet will genereate the
following:

Table
ID, A, B, ..., Z, TS

SELECT ID, A, B, ..., Z, TS
FROM Table
WHERE ID= AND TS=

This will avoid the rounding error and save network bandwith.


mfG
--> stefan <--