# Solved: Access w/ ODBC MSSQL - Write Conflict



## djkelley (Mar 13, 2006)

I have an Access table linked to MSSQL. The table has a primary key with no additional indices. I receive write conflict errors when I attempt to update some records (not all) while in an access form or directly in the table via Access. Changes in SQL work fine. Very strange - some records can be changed, while others cannot, seemingly without any pattern related to position in the table or data contents. NO triggers exist in the DB. What is going on?


----------



## cristobal03 (Aug 5, 2005)

Hey there, welcome to TSG.

First of all, I'm not sure position within the table would ever matter because, technically speaking, a record has no position within a table until the records have been sorted somehow.

Now, are you the only one accessing the back end at the time you try to create a record? In other words, is this a locking issue? Or are the errors of a different nature? You say there is no data type issue, can you confirm this (e.g., trying to place a long integer in an attribute of data type integer/short)?

chris.


----------



## djkelley (Mar 13, 2006)

No other users are accessing the MS-SQL DB. Some columns are INT but all data in all records is completed, no nulls, etc. I have deleted the linked table and re-linked. Still the Write Conflict while attempting to change contents of a column on selected records. I might add that it is always the same records that will not re-write. That to me says that it is the data or ACCESS protecting certain records????


----------



## djkelley (Mar 13, 2006)

If the DB has a bit column. I routinely use this column type to hold Yes/No or True/False values. If the record has one of these columns and the contents of that column is FALSE or Zero, then the data cannot be rewritten. If the contents of the Bit column is true the record rewrites without a problem!!!!!! 

Yuck! How do I correct this without abandoning the use of Bit column types?

djk


----------



## cristobal03 (Aug 5, 2005)

There must be something else going on. What is the text of the error message you receive?

chris.


----------



## djkelley (Mar 13, 2006)

See Attached png message image.


----------



## djkelley (Mar 13, 2006)

Apparently if the table is defined to contain a Bit column type and a record's value for that column is Null then Access and SQL cannot negotiate an Update for the record. the resolution therefore is to set the default for bit columns to 0 or 1, not allow null values, and correct any data in the DB with an MS-SQL update that sets all Null values in bit columns to zero.

Thanks to Cristolbal03 for shaking loose particles free in my brain to research this resolution.


----------



## cristobal03 (Aug 5, 2005)

Ah, I see. Yes, I don't know how many DB engines support triple-state bit switches. I was assuming unswitched values represented 0 not Null.

:up: Glad you got it. You can mark this thread "Solved" using the *Thread Tools* at the top of the page.

chris.


----------

