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

Adriano Torres 1 Reputation point
2020-08-25T15:51:46.033+00:00

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.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
6,070 questions
{count} votes

3 answers

Sort by: Most helpful
  1. David Watson 1 Reputation point
    2020-08-26T01:35:56.977+00:00

    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.

    No comments

  2. Michael 17,861 Reputation points
    2020-08-26T09:18:49.507+00:00

    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.

    20525-image.png


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

    No comments