Microsoft KB Archive/209569

= ACC2000: SQL Server Views Are Read-Only When Linked =

Article ID: 209569

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209569



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



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



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



RESOLUTION
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:

CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)

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.

