ACC2000: SQL Server Views Are Read-Only When Linked (209569)


The information in this article applies to:

  • Microsoft Access 2000
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.

MORE INFORMATION

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.

REFERENCES

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:

209807 ACC2000: Remote ODBC Tables Are Read-Only Without a Unique Index

209123 ACC2000: Creating Virtual Indexes w/ SQL Data-Definition Queries

Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbprb kbusage kbWorkFlowDesigner KB209569
©2004 Microsoft Corporation. All rights reserved.