We all know the situation that we have to update a large SharePoint list. Using the datasheet view option in SharePoint to update columns is not always adequate because it has some limitations. Update a field with external data or use some complex logic to filter the data is not possible. Here at Spikes we know that in these situations the integration of SharePoint with Access can help you a lot. In this blog I will focus on some interesting functionalities of this integration.

View and use lists in MS-Access

To open a list in Access is very easy. Open your SharePoint list and click on the option ‘Open with Access’ in the list ribbon menu.

Specify the location and name of your access database and select the kind op connection:

  • Use ‘Link to data on the SharePoint site’ if you plan to do updates in Access or like to use the same connection in the future.
  • Use ‘Export a copy of the data’ if you only need an copy of the list in Access

Now Access has created a new database and added the SharePoint list as a table. Here you can use all the functionalities of Access on this SharePoint list like creating queries, reports and graphs.

Update list in Access

Access is not only useful to view the list and generate some nice reports. As I already told we can also update SharePoint lists in Access.

In my example I have a SharePoint list ‘AdditionalContract’ with contract information that is now linked with another Access table ‘ContractInfo’ containing the end date of the contracts.

In Access I added a new column ‘ContractEndDate’ to the Sharepoint list and created a query to update this new fields with the information from the table ContractInfo.

After running this query the new column is added to the Sharepoint list and ContractEndDate is filled for the matching contract numbers.

As you could see it is easy to use Access to update a SharePoint list. So when you need to update a list or upload external data to a SharePoint list consider to use Access as a quick solution. This integration is available for all SharePoint versions, including online.

Patrick Wegge

SharePoint Consultant @ Spikes