ACC: Attached SQL Server Table Cannot Be Updated

ID: Q129166

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you try to make changes to a linked (attached) SQL Server table, you cannot update the table.

RESOLUTION

To work around this behavior, check that the linked table has a unique index and that the unique index does not contain a timestamp field. If the unique index does contain a timestamp field, drop the index and create a new unique index on a field other than a timestamp field.

MORE INFORMATION

A timestamp field contains a unique binary value generated by SQL Server that is updated whenever the record is updated. Microsoft Access uses the value in the timestamp field to determine whether a record has been changed before updating it.

Steps to Reproduce Behavior

In a Microsoft SQL Server utility (such as isql/w) run the following commands:


      create table TestTimestamp (field1 varchar(10), timestampfield
      timestamp) go
      create unique index TestTimestamp_Index on TestTimestamp (field1,
      timestampfield)

In Microsoft Access, attach the TestTimestamp table. Note that when you open the table in Datasheet view, you cannot edit or add new records.


Keywords          : kbusage OdbcSqlms 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 9, 1999