Jun 29 2021 04:43 AM
Jun 29 2021 04:43 AM
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 Not able to open Access Database Files in SharePoint Online ), 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.
Jun 29 2021 07:21 AM
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.
Jun 29 2021 07:32 AM - edited Jun 29 2021 08:12 AM
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.
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.
Jun 30 2021 12:33 AM - edited Jun 30 2021 12:38 AM
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.
Jun 30 2021 01:42 AM - edited Jun 30 2021 02:21 AM
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.
Jun 30 2021 03:58 AM
Jun 30 2021 04:22 AM - edited Jun 30 2021 04:23 AM
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.
Jun 30 2021 04:35 AM
Jul 02 2021 12:50 AM
Jul 02 2021 02:08 AM - edited Jul 02 2021 02:08 AM
Jul 02 2021 05:55 AM
Jul 02 2021 08:46 AM
I hope my reply wasn't misunderstood - my apologies if it was - your replies have helped me and I am grateful to you both! I guess silence is a message - it just takes a good ear to catch the nuances!
My original plan was a macro to append directly to the SP list, but I couldn't find any guidelines on linking to SP Online and then found this blog Using VBA And ADO To Pull Data From SharePoint Lists that suggested I never would.
So I diverted to Access and had that running in my local environment by converting the Access table to a local table and converting the UserInfo column to text in Access. But I can see now that I would run into the problem @George Hepworth sketched in the example - if I get it working in Access, then it won't work in the SP list, and vice versa.
I'd imagined this was a pretty straightforward thing to do, but I'm wiser now!
Many thanks again!
Jul 02 2021 09:01 AM
I'm still not sure what you are trying to accomplish. So, I just want to add in general that if it's about sharing some data between the desktop and Sharepoint, then linked Sharepoint lists/tables in Access might be the right solution. I've been using this combination for several years in some solutions for clients when it's a few thousand records.
There are some limitations with it, but where aren't there? The point is that they are known and taken into account. To say more here, one would need to know or understand more about your intentions.
Jul 02 2021 09:25 AM
Thanks again @Karl Donaubauer
I can see linked SharePoint lists/tables in Access are a working solution, but what I want to do is have both manual and automated updates to the SP lists.
We have users adding data manually to the SP lists through forms, but we also have repetitive data that is currently added manually every week. And in Excel I can set up a sheet with columns using formulas TODAY and NOW and the new repetitive data.
I can then use Task Scheduler to upload on a given day and time and uploading the repetitive data can be automated.
Maybe, maybe, maybe... there is a way in SP Online to import at a scheduled time, rather than export from Excel, but I don't think there is such a way.
Jul 02 2021 09:38 AM - edited Jul 02 2021 09:39 AM
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.