PRB: "Record Has Been Changed" Error After Upsizing (163994)

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.

This problem does not occur with SQL Server 6.0 or with Microsoft Access 97.

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:

152021 : PRB: Deleting Records Containing NULLs Using DAO

WORKAROUND

To work around this problem, do the following:

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.
In Microsoft Access, refresh the recordset or close and open the table again.

Now updates will work, because Microsoft Access will use a value for the timestamp, instead of null.

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.)