Bulk data insert into SharePoint list using access, Facing "Cannot update. Database or object is read-only" issue

Question

I have written a query to insert data into the SharePoint list which is linked to that database.

In some cases, I am able to insert data successfully but in other cases, after some time it showing "Cannot update. Database or object is read-only" and stops the execution, inserting few records in the SharePoint list.

Answer 1

Hi harsha,

Thank you for posting back and shared the information and cause with us.

We have read your description carefully. And it seems that you want to delete the old records in SharePoint list everyday and new data from Excel file. And you assume that the problem is related to network fluctuations, and it occurs randomly.

If my understanding is right, this issue may be related to that you inserted bulk data and you used Access to delete old records and update new records, due to the number of data is too large, during this period, the server may be overloaded and unresponsive, so multiple connections are required to succeed.

For the three questions you provided below, we want to provide some workarounds here to get rid of this behavior and make it easier:

1. Since you mentioned that you want to delete old records and deploy new records, if so, you can refer to this article: Create a list based on a spreadsheet to create a SharePoint list by importing an existing spreadsheet file. And in this way, you can delete the list with old records or save it, then you can create a new list with new records every day.
2. And according to our search, and we found your requirement may be achieved by Microsoft Flow. If you want to use Microsoft Flow to deploy it,

to make sure you get professional help, we’d suggest you post a new thread in Power Automate Community, that’s a specific channel to handle related questions. And we also searched a lot and we found the related thread, for your reference: Update SharePoint List from Excel file. 3. For the third workaround, and you can also use PNP code to delete all records in SharePoint list, and we have tested it on our side and it works fine, you may need to wait some time, and we will send the detailed steps in private message, you can see the detailed steps I provided in private message.

Hope the workarounds above can help you.

Best Regards,

Sukie

Answer 2

Hi harsha,

Thank you for your message in this forum.

According to your description, it seems that you encountered an error message “Cannot update. Database or object is read-only” when you used a query to insert data into the SharePoint list. At the same time, some cases work fine, and for the other cases, it failed.

If my understanding is right, we have searched a lot according to the error message you provided, and it mentioned that this problem occurs when user use the ImportSharePointList macro action to link to a view of a SharePoint list in Access. This macro creates linked tables in Access for each lookup column in the SharePoint view. However, when the update query runs, it first checks that all lookup columns have linked tables in the database for the underlying list. The update query does not check exclusively for the lookups that are part of the current query.

For more details, you can refer to this article: “Cannot update. Database or object is read-only” error in a query against a linked SharePoint view if there are unlinked lookup fields in Access. At the same time, you can also refer to the workaround in this article to check if it will help you:

If the suggestion above doesn’t work, could you provide the information below?

1. Could you provide the Product information and version of your Access? You can go to Access > File > Account and provide the full screenshot of Account page.
2. Could you provide the screenshot of error message?
3. Could you provide the detailed steps about how you used the query to insert data into the SharePoint list?

Best Regards,

Sukie

Answer 3

Hi Sukie,

I am inserting 6000 records using an access database to the SharePoint list.

I have added a SharePoint list to access using -> External Data --> New Data Source --> From online services --> Sharepoint list

In the same way, I have added excel from my local computer.

And I have created a query design

Create --> Query Deign --> Append 

I am selecting data from excel and appending to the SharePoint list.

In some cases, few data are appending to the SharePoint list and stops by saying "Cannot update. Database or object is read-only"

when I review the SharePoint list it showing like 1400 or 3000 records instead of 6000 records.

Answer 4

Hi harsha,

We appreciate that you posted back and provided the so detailed steps.

We may need some time to test it on our side according to the detailed steps you provided. And if we have any updates, we will post back and provide our test results.

At the same time, to better understand your situation, could you also provide the information below?

1. As I mentioned above, could you provide the Product information and version of your Access? You can go to Access > File > Account and provide the full screenshot of Account page. So we can also test it on our side according to the version and Product Information you provided.
2. We noticed that you mentioned that you can only see 1400 or 3000 records in SharePoint list. To narrow down this issue, could you also test with fewer records to check if the same problem will occur? And if it is convenient to you, you can also use another SharePoint list to check if the same problem will occur.
3. Could you provide the screenshot of error message?

Your understanding and patience will be highly appreciated.

Best Regards,

Sukie

Answer 5

Hi harsha,

Thank you for your patience so far. I’m sorry for the late reply.

We have tested it on our side according to the steps you provided, as steps shown below:

We went to Excel and created a new workbook with 5999 rows data and a row of title, then we went to Access > External Data > New Data source > From online services > SharePoint list and typed the link, as shown below:

Then we went to External Data > New Data source > From File > Excel file > Selected the file and selected Append a copy of the records to the table, then selected list table, as shown below:

Then we went to SharePoint Online and we found that we can see 5999 records and cannot reproduce the same situation as yours, as shown below:

If the steps above are consistent with yours, to narrow down this issue, could you provide the information we confirmed above

1. Could you provide the Product information and version of your Access? You can go to Access > File > Account and provide the full screenshot of Account page.
2. We’d suggest you create a new database in Access and new Excel file to check if the same problem will occur.
3. Do all lists and sites have the same problem? You can try another list to check if the same problem will occur.
4. Could you also provide the screenshot of error message?

Best Regards,

Sukie