ACC2000: SQL Server Views Are Read-Only When Linked

When you create queries based on linked tables, the queries are read-only and cannot be updated.

This behavior occurs because there is no unique index (key field) set in the linked table.

Microsoft SQL Server supports views that can be updated. Microsoft Access can only link these views as read-only because SQL Server views do not have indexes. Microsoft Access requires a unique index on the linked object for update/delete/insert capability.

You can either redefine the table to give it a unique index, or you can create a unique index by using a data-definition query.

For example, you can run the following Access data-definition query: It is run on a linked view named SeptemberOrders that returns a subset of the remote Orders table and has a unique field named OrderID.

Steps to Reproduce Behavior

To create a read-only view on a linked table, you must be using Windows 98 or Windows NT 4.0 with a version of Microsoft Data Access Components earlier than MDAC 2.5. Follow these steps:

1.	Start Microsoft Access and open the sample database Northwinds.mdb.
2.	On the File menu, click Get External Data, and link to the Authors table in the Pubs database.
3.	Create a query based on that linked table without specifying a unique index.
4.	Open the view in Datasheet view and attempt to change data in any record. Notice that the query is read-only and that Access will not allow you to update the data.

For additional information about Microsoft Access and updating linked SQL Servertables, click the article numbers below to view the articles in the Microsoft Knowledge Base: