Forum Discussion
Force Access DB out of read-only mode
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! 
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 https://scottlyerly.wordpress.com/2014/05/14/excel-geeking-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
- Nope - same problem with a query.
Aha! Run an Access query instead! Super suggestion, thank you!
I am going to have a weekend now, but will work on this!Many thanks!
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.comThanks 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,
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
