ACC: Attached SQL Server Table Cannot Be Updated

Last reviewed: May 30, 1997
Article ID: Q129166

The information in this article applies to:

Microsoft Access 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
Hardware : X86
Issue type : kbprb
Resolution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.