Force Access DB out of read-only mode

Copper Contributor

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.

16 Replies

@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.

 

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

@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!

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

 

 

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

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

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

 @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

Hi,
My personal silence = I have no idea what you are trying to accomplish. That's why I let others answer who understand you better. So far there was just no crowd. ;)

Servus
Karl
*********
http://AccessDevCon.com
https://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.

Thanks @George Hepworth and @Karl Donaubauer 


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! :smile:

 

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!

 

Charles

Hi,

 

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.

 

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

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.

 

Charles

 

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.

 

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

 

@Karl Donaubauer 

Aha! Run an Access query instead! Super suggestion, thank you! :smile: I am going to have a weekend now, but will work on this!

Many thanks!

Nope - same problem with a query.