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. And here I am having a problem, because the Access table is read-only. In the SP list there is a column with data type "Person or Group", but for some reason in Access that column has data type "Number" - seeing person names are not numeric, the data upload causes a type mismatch error. Also Access has decided it is a required value (which it isn't in the SP list). You cannot open an Access DB directly in SP Online (see https://answers.microsoft.com/en-us/msoffice/forum/all/not-able-to-open-access-database-files-in/0541dfdd-cb2a-41e9-b620-0f8551d244d5 ), but have to download it, and the downloaded read-only version does not permit e.g. changing data types. All this seems pretty weird and I am wondering if there is a way to force an Access database out of read-only mode. I presume converting the table to a local table breaks the linkage to the SP list.

CharlesDK It sounds like you have at least two different, albeit related problems.Before attempting to sort it out, though, we need a bit of clarification. HOW did you create the SharePoint list in the first place?HOW did you link the Access accdb to that SharePoint list?Does the SharePoint list have a field defined as a Primary Key, i.e. a unique identifier? If so, what is that field and what datatype is it?I suspect that the "Person or Group" field in your SharePoint list is actually defined in SharePoint as a Lookup field. If so, it really IS a number and that number is actually the Foreign Key from whatever SharePoint list it gets the Person or Group values. Please confirm that. The issue as to whether it is required or not is going to be determined by some of the other factors we identify, but my guess is that SharePoint and Access have agreed that it is, in fact, required and you just need to figure out why.

Hi, I'm not sure to understand your situation. So, if you don't get helpful answers it might be good to describe step by step what you did to create the Access file, where it is, if the complete file, the table or only the Person field is not editable, how you linked the Sharepoint list etc. Generally said: The data type of the Person field has to be a number of type Long in Access because it is the same in Sharepoint. This column refers to the ID column in the UserInfo list of Sharepoint. Normally when you link a Sharepoint list in Access which contains a Person field the UserInfo list is linked automatically too and the lookup relationship that exists in Sharepoint is also created in Access. Access then shows a combo box in the linked table/list to let you choose a person (or group) by name instead of ID. If you don't see the names but numbers in the column/combo box then it may be that your Access db is missing the UserInfo list. In this case you can try to right click on the linked table in the navigation pane of Access and choose More Options and Refresh List. This should link the UserInfo list, create the relationship and let you see and choose the person names in the combo box. ServusKarl*********http://AccessDevCon.comhttps://www.donkarl.com

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. ServusKarl*********http://AccessDevCon.comhttps://www.donkarl.com

Silence is a message itself.You are experiencing a problem with uploading Excel data into SharePoint lists. Both are at the perimeters of my knowledge in the first place. In addition, data problems tend to be harder to suss out anyway because, well, the data itself is part of the context and since we can't see that data, it's harder to try things out.In the initial question, you suggested "forcing Access out of read only mode", but that hardly seems to be the problem, nor would it be the solution, IMO. The problem appears to be in either the imported Excel data or in the table/SharePoint list or both.FWIW, I recently tried to move a table with calculated fields to SharePoint. Access handles the locally defined calculated values (concatenation of First and Last Names of People). SharePoint shows an error when that same calculation is ported over to the SP list. I can then "correct" it in SP so it shows there, but then the linked SP list raises an error every time I try to open it in Access. I don't know why. It does suggest to me that although basic tables/lists are okay, the internal differences between the way tables are handled and the say SP lists are handled are tricky.I'm going back to the Excel data as the most likely source of your problem, but that's 90% a guess.

Hi, In Access, it is still much easier to generate repetitive data because you can use queries with expressions etc. for this. A Windows task to start and run an Access application is simple and done x times, too. The problem could rather be the data volumes. On the one hand with the probably necessary append queries, which run with at most 30 records/second for linked SP lists. On the other hand, the performance can get bad when displaying/handling the data in Access, if there are many records in the SP lists. Therefore the data volumes are important. ServusKarl*********http://AccessDevCon.comhttps://www.donkarl.com

Force Access DB out of read-only mode | Microsoft Community Hub

16 Replies

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!
- 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
  - 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
MVP
Jun 29, 2021
Hi,

I'm not sure to understand your situation. So, if you don't get helpful answers it might be good to describe step by step what you did to create the Access file, where it is, if the complete file, the table or only the Person field is not editable, how you linked the Sharepoint list etc.

Generally said:

The data type of the Person field has to be a number of type Long in Access because it is the same in Sharepoint. This column refers to the ID column in the UserInfo list of Sharepoint. Normally when you link a Sharepoint list in Access which contains a Person field the UserInfo list is linked automatically too and the lookup relationship that exists in Sharepoint is also created in Access. Access then shows a combo box in the linked table/list to let you choose a person (or group) by name instead of ID.

If you don't see the names but numbers in the column/combo box then it may be that your Access db is missing the UserInfo list. In this case you can try to right click on the linked table in the navigation pane of Access and choose More Options and Refresh List. This should link the UserInfo list, create the relationship and let you see and choose the person names in the combo box.

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com
George_Hepworth
Silver Contributor
Jun 29, 2021
CharlesDK
It sounds like you have at least two different, albeit related problems.
Before attempting to sort it out, though, we need a bit of clarification.

HOW did you create the SharePoint list in the first place?
HOW did you link the Access accdb to that SharePoint list?
Does the SharePoint list have a field defined as a Primary Key, i.e. a unique identifier? If so, what is that field and what datatype is it?
I suspect that the "Person or Group" field in your SharePoint list is actually defined in SharePoint as a Lookup field. If so, it really IS a number and that number is actually the Foreign Key from whatever SharePoint list it gets the Person or Group values. Please confirm that. The issue as to whether it is required or not is going to be determined by some of the other factors we identify, but my guess is that SharePoint and Access have agreed that it is, in fact, required and you just need to figure out why.

Forum Discussion

Force Access DB out of read-only mode

16 Replies

Resources