Updating Linked SQL 2000 Table Creates Access "Write Conflict"

  • Thread starter Thread starter Hugh Mulholland
  • Start date Start date
H

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?
 
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.
 
Back
Top