Forum Discussion

CharlesDK's avatar
CharlesDK
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...
access
sharepoint
CharlesDK's avatar
CharlesDK
Copper Contributor
Jun 30, 2021

Karl_Donaubauer George_Hepworth 

Thanks George and Karl - I can sense yours are the right questions!

 

At present I have only a test setup - an empty SP list that I copied from an existing list (to replicate the data structure). It doesn't use a primary key, but if I recall correctly, when the original list was created (most likely as an export from Excel), it did require me to add a Title column, which is now an unused text column.

 

I created the Access file as an empty DB in Access and linked to the new list in SharePoint Online: External Data -> New Data Source -> From Online Services -> SharePoint list. The imported table was renamed Test1. The accdb file is saved in SharePoint Online (same site as the SP list).

 

Yes, Person or Group uses the UserInfo list and I suspected that might be a numeric value, so thanks for confirming that! The UserInfo table is also imported into the Access DB with a relationship to the dataset table and is populated - the names are shown in a drop-down when the Name field is selected.

 

BTW my macro works fine with a locally saved accdb file in which the relationships are removed and data type changed - just to eliminate that as a potential problem.

 

Many thanks!

    • CharlesDK's avatar
      CharlesDK
      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's avatar
        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

Resources