Updating Linked SQL 2000 Table Creates Access "Write Conflict"

[Hugh Mulholland]

After adding fields to a linked SQL 2000 table (using EM), then updating the
link in an Access 2003 .mdb file to reflect those additions creates an
"un-updatable" table in Access (but the table is updatable in SQL Server).
Any data modification attempted on the table data thereafter produces a
"Write Conflict" error message stating that the record has been modified by
another user since you started editing it ... I know this to be false. It
only happens when the table(s) in question have been copied from one SQL
Server database to another using import/export (DTS) AND affects both the
sending and receiving table! This has happened to me twice, in 2 differend
databases.

Has anyone else experienced this problem?

Is there a solution other than my workaround of importing the table back
into Access, deleting the SQL Server version of the table, and upsizing the
table back to SQL Server 2000?

 

[Joe Fallon]

Very common problem.
You need to *delete* the link and re-create it.
Do not *refresh* it.

Also, do you have a PK in the table?
Any uncommon datatypes? Common ones are Char, VarChar, Integer, Bit.
Decimal datatypes are known to produce this error.

The simplest thing to do is add a field to the table using the timestamp
datatype.
This way Access does *not* perform a field by field comparison when updating
(which is the ccause of the problem since decimal compares are not "equal"
they are off by a factor of a billion or so.) If a Timestamp column exists,
Access will simply see if it has changed since the record was read and then
update the record if it has not.