Forum Discussion

Copper Contributor

Jun 29, 2021

Force Access DB out of read-only mode

In order to use Task Scheduler to update a SharePoint Online list from Excel, which it doesn't seem possible to do directly using VBA, I am trying to update via an Access DB linked to the SP list. An...

Karl_Donaubauer

MVP

Jun 30, 2021

Hi,

If the Primary Key, i.e. ID column, is missing that could make the linked table read-only. Have a look at this article on how to add a Unique Identifier. After any change to the list refresh the linked table.

The Title column is mandatory in a Sharepoint list but it can be empty.

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

Copper Contributor

Jun 30, 2021

Hi,

I have added the ID column, but I still get error message "table is a linked table whose design can't be modified ... you must do so in the source database".

Also saw a couple of other questions, so for good order adding that it's not possible to open an accdb file directly in SP Online, so it has to be downloaded, and the file isn't set as read-only. The UserInfo table is also read-only.

Thanks again Karl!

Charles

Karl_Donaubauer
MVP
Jun 30, 2021
Hi,

One thing is if you are able to change the data in the linked table, e.g. add a record or change a field's content or a person in a record.

The other thing is the design of the table, like adding a new column:

"table is a linked table whose design can't be modified ... you must do so in the source database"

What are you doing to get this message? It is what you get when you try to change the design of any linked table in Access (not only Sharepoint). Sharepoint is even an exemption from the rule as in datasheet view of the linked table you can change parts of the design via the Fields tab of the ribbon. But I'm not sure why you would want that.

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com
- CharlesDK
  Copper Contributor
  Jun 30, 2021
  Hi,
  I can add and edit data in the Access table and that is reflected in the SP list, so that works fine. It is the Excel upload that doesn't work.
  If I have opened the table, the error message occurs immediately when I change to Design View.
  Charles
  - CharlesDK
    Copper Contributor
    Jul 02, 2021
    Karl_Donaubauer George_Hepworth
    I guess silence = no-can-do! I would have thought automated updates to SP lists was a pretty usual thing to want to do, so I will try to find an alternative method (any suggestions welcome!).
    Many thanks for your kind attention to this!
    Charles

Resources