Extremely slow while updating a large Sharepoint Documents Library Metadata Using Microsoft Access 2016

Question

We have created this documents library in our corporation to handle engineering drawings. I migrated the files (~73,000 files and folders) and then tried to import metadata from and old excel table.
I connected to the sharepoint Documents library using MS Access 2016 and created a table in the same database with all the metadata I had, some 15,000 rows and 10 columns.
Then I created an update query in Access to update the Documents library columns with my metadata from the table I had imported.
It took many hours to update one single column and now I am updating the other nine. While updating, Access would not respond and appear as if it had crashed. Before using the update query, I tried simply cutting and pasting the data in the Document library table. That was a disaster, Access would crashed everytime.

So, the problem is that this operation is very slow and unreliable, it makes no sense that it is taking so long. And it may probably rule this idea, of having our drawings repository in sherapoint, a failure.

I have seen old posts about an old windows patch that made this kind of connection be very slow. Could somebody have a workaround for this?

I appreciate it.

Answer 1

I am not sure from the description, however I suspect it may be quicker in MS Access to use recordset to update rather than an update query.

If you are familiar with DAO this may work. I would tend to use ADO for this.

Be worth a try and see if that speeds the data transfer up.

Answer 2

I'm not familiar with the Microsoft Access. As a workaround, you could connect to SharePoint Document library using excel. Then update Library Metadata in the table, check if this works for you.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

This fixed it for me... https://learn.microsoft.com/en-us/office/troubleshoot/access/access-crashes-updating-linked-list#:~:text=To%20do%20this%2C%20follow%20these%20steps%3A%201%20In,Access%202010%20and%20latercheck%20box%20is%20selected.%20