You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000

You receive the following write conflict error when you try to update records in a linked SQL Server table: You are then given the following options: Save Record, Copy to Clipboard, or Drop Changes.

Access is creating Null bit fields, which causes a type mismatch.

or

The linked table is using fields defined as floating point data type (i.e. Real). This data type allows for storing "floating point" number data. When the edited record is passed to SQL Server, if both engines see the data in these fields as having the identical value, there is no problem. However, because of the "rounding" algorithms used by JET and SQL Server, the actual value compared may be different. Since JET has to review each field and see if there are any concurrency issues, when JET compares the number it sees (in the "Real" data type field) to that value stored in SQL Server, if it does not match exactly the record is assumed to have been changed and a Write Conflict occurs.

To resolve the problem with bit data type, do one of the following:

• Using SQL Server, open the table in Design view, and assign a default value of 0 (zero) on all bit fields.
  
  NOTE: With this option, you must update records that were entered before this change was made. See the next item for more information.
  
  
• Using SQL Server, run an Update Transact-SQL statement against the table, and set all bit fields that have a value of Null to a new value of 0 or 1.
• Using SQL Server, change the bit fields to some other data type, such as int or tinyint.
• Using SQL Server, add a timestamp field to the table.
• Use a Microsoft Access project (*.adp) instead of an Access database (*.mdb).

To resolve the problem with floating point data types, do one of the following:

• Add a timestamp column to the SQL table. (JET will then use only this field to see if the record has been updated.)
• Modify the data type that is in SQL Server to a non-floating point data type (for example, Decimal).
• Run an Update Query to update the record. You must do this instead of relying on the recordset update.

NOTE: If you make changes to the data types in the SQL Server tables, relink the tables in Microsoft Access.

Steps to Reproduce Behavior

The following steps assume that you have an understanding of how to create tables in SQL Server, and that you are familiar with certain SQL Server tools such as Enterprise Manager.

Also assumed is that you are aware of how to create File, User, and System Data Source Names (DSN), and how to use a DSN to link a table to a Microsoft Access 2000 database.

1. In Microsoft SQL Server 7.0 or later, add a new table to the Pubs sample database that has the following table structure:
   Collapse this tableExpand this table

   Column Name	Data Type	Length	Allow Nulls
   fldID	int	4	<unchecked>
   fldBit	bit	1	<checked>
   fldDescrip	varchar	50	<checked>

   
   
2. Make the following Column property assignments to the fldID field:

      Identity: Yes
      Identity Seed: 1
      Identity Increment: 1
   					

3. Set the fldID field as the primary key, and then close and save the table as Table1.
4. Insert the following records into the Table1 table:
   Collapse this tableExpand this table

   fldID	fldBit	fldDescrip
   1	1	Record #1
   2	<NULL>	Record #2
   3	0	Record #3
   4	1	Record #4

   
   
5. Close the table, and then create a User DSN that points to your SQL Server.
6. Open any Access 2000 database, and then link the Table1 table from the Pubs database into your database.
7. Change the description of Record #1 to Record #0. Note that you can save your change.
8. Try to change the description of Record #2 to Record #1. Note that you receive an error when you try to save your change.