Fastest Way to Edit Large SharePoint Lists

Introduction

Today I realized that few people know it is possible to edit SharePoint lists directly via Microsoft Access. Microsoft Access is included in most Microsoft 365 subscriptions and has several advantages over the out-of-the-box browser-based SharePoint features.

The edit in grid view feature is great for some basic scenarios, but it won't cut it when you need to work with many thousands of records.

Exporting to Excel is limited to 5000 items, and it's read-only. Export to CSV is also read-only, and the resulting CSV file has no service connection to SharePoint, meaning that you need to export a new CSV file each time something is updated.

PowerShell PnP is still an option for working with large lists. However, most users are not familiar with it or don't have the technical ability to run such automation in their secure corporate environment.

Microsoft Access to the Rescue

Microsoft Access, among many other services, can connect to SharePoint lists. I'll show you.

Assuming you have M365 Office installed, you should also have MS Access at no additional cost. So, you can launch it on your computer.

First, let's create a new blank Access database.

• Next, let's create an external connection to a SharePoint Online list:

• Paste a URL to a SharePoint site that contains a list.
• Make sure "Link to the data source by creating a linked table" is selected:

• Select one or more SharePoint lists:

• Now you can edit the loaded SharePoint lists directly from Microsoft Access:

• The changes in MS Access will immediately be reflected in the SharePoint Site:

Advantages of Using Access

Although rare, there are a few valid use cases for using Microsoft Access with SharePoint:

1. Excel-like user interface.
2. No software programming skills are required.
3. Ability to work with large SharePoint lists.
4. Perform large bulk operations.

Supported Features in MS Access

• Working with lookup fields, User fields, file attachments, choice fields, etc.
• Ability to create new list columns right from Access
• Hide/Show/Delete columns

Limitations of using MS Access

• You must click "Refresh" in MS Access to get fresh data from SharePoint.
• More than one person can't open the same MS Access database.

Conclusion

Next time you need to edit a large SharePoint list, you will remember that Microsoft Access is an easy and free tool available in your toolbox.