Error (Cannot update. Database or object is read-only) in a query against a linked SharePoint view if there are unlinked lookup fields in Access

Symptoms

While you work in Microsoft Access, you run update queries against a linked Microsoft SharePoint view. The underlying list that you are updating includes lookup fields that are not linked to Access because they are not included in the current view.

In this scenario, you receive the following error message:

Cannot update. Database or object is read-only.

Cause

This problem occurs when you use the ImportSharePointList macro action. (For Access 2016 and later) or TransferSharePointList action (for Access versions earlier than 2016) to link to a view of a SharePoint list in Access.

This macro creates linked tables in Access for each lookup column in the SharePoint view.

Although the update query first checks that all lookup columns have linked tables in the database for the underlying list, it does not check exclusively for the lookups that are part of the current query. Because of this condition, the database connection is severed. Therefore, the query returns a read-only object or database in a write operation.

Workaround

To work around this problem, use one of the following methods.

Method 1

Link all the lookup columns in the underlying list to tables to make them writable. To do this, follow these steps:

  1. Link to the SharePoint list itself. This makes sure that all lookup tables for the underlying list exist within Access.
  2. Delete the linked table for the SharePoint list in Access. This leaves as intact the linked tables for the lookup columns.
  3. Link to the SharePoint view by using the ImportSharePointList macro action.

After you implement this workaround, Microsoft Access will contain a linked table for the SharePoint view. It will also contain the linked tables for all the lookup columns in the underlying list, not only for the lookup columns that are included in the view.

If these steps do not resolve the issue, go to Method 2.

Method 2: Disable caching in Access 2010.

To do this, follow these steps:

  1. In Access, select File > Options.
  2. Select Current Database.
  3. Scroll down to the Caching Web Service area.
  4. Locate the Microsoft SharePoint tables.
  5. Select the Never Cache check box.

More information

For more information about known issues that occur when you use SharePoint lists in Access, see Access cache formats for SharePoint lists and document libraries.

For more information about the ImportSharePointList (TransferSharePointList) macro, see ImportSharePointList Macro Action.## More information

Still need help? Go to Microsoft Community.