PRB: "Record Has Been Changed" Error After Upsizing

The information in this article applies to:

• Microsoft SQL Server, version 6.5

SYMPTOMS

If you move a table from Microsoft Access 95 to SQL Server 6.5 with the Upsizing Wizard, and if the Upsizing Wizard added a timestamp field, Microsoft Access produces a dialog box titled Write Conflict that contains the following error:

   This record has been changed by another user since you started
   editing it. If you save the record, you will overwrite the changes
   the other user made.

   Copying the changes to the clipboard will let you look at the values
   the other user entered, and then paste your changes back in if you
   decide to make changes.

CAUSE

The Upsizing Wizard moves the data to the server and then sends an alter table statement to add the timestamp field. This adds the timestamp field, but leaves it blank. After the upsizing is complete and it sends the update to the server, Microsoft Access sends the update with the timestamp value of null, but it uses the SQL Server 6.0 syntax (that was appropriate when the Upsizing Wizard was written). However, this procedure fails with SQL Server 6.5. This problem is caused by the change in the default ANSI_NULLS option for the driver. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q152021
   TITLE     : PRB: Deleting Records Containing NULLs Using DAO

WORKAROUND

To work around this problem, do the following:

1. Use ISQL\W or Enterprise Manager to update every record in the table by sending a query that updates one of the fields to itself. For example, use the following query:

         UPDATE newtable
         SET zipcode = zipcode
   

   This method updates every record without changing the content. By updating each record, the server provides values for the timestamp field.

2. In Microsoft Access, refresh the recordset or close and open the table again.

MORE INFORMATION

Another workaround is to upsize the table without data. Then link to the table and move the data into it with an append query. (AutoNumber/identity fields require the use of IDENTITY_INSERT.)