Write conflict on existing records in Access>SQL app

[Matt Lusher]

I've got a DB app that uses an Access .mdb file as the
front end, connecting to a SQL Server 2000 back-end db
file. I recently added a column to a table in the SQL db
to store a bit type, and I modified my Access front-end
to expose that column as a checkbox control on my main
data entry form. Now, I can't update any existing
records; I get a write-conflict error saying someone else
has changed the record since I last viewed it. Any idea
why? Notes: my previous version of the frontiend has no
problem updating existing records in the SQL db, even
after the added column. Also, in the new front-end, I get
the same write-conflict error no matter what value I try
to update, not just the new yes/no checkbox.

 

[Matt Lusher]

Thanks for the suggestion! We discovered that our problem
was the way the column had been added in the SQL table.
We had added the column without specifying a default
value; SQL filled the column with nulls. We deleted the
column and re-added it, this time specifying a default
value of 0 (=false). Access then had no problem.

 

[Razvan]

Hello.

The error is created by the newly added columm (of a bit
type). When you create that column SQL automatically
populates it with NULLs for all the records already in the
table. Access does not like the NULLs in the bit-type
column and so it produces the error.

For a detailed description of this issue, read Article
278696 in the Microsoft Knowledge Base (ACC97: Write
Conflict Error When You Try to Update Records in a Linked
SQL Server Table).

What you need to do, is remove the accept NULLs option in
the design of the new column, and possibly give it 0 as a
default value.

Razvan