Connecting to SharePoint (WSS3/MOSS2007) from MS Access 2007


By:   |   Updated: 2010-04-20   |   Comments   |   Related: > SharePoint


Problem

With WSS2, I used to be able to read and write to SharePoint lists from MS Excel 2003. With WSS3 and MOSS 2007, that functionality seems to have been disabled. Does that functionality still exist?

Solution

Yes, in WSS3 and MOSS 2007, that functionality from MS Excel (any version) was removed. However, that functionality was reassigned to MS Access (2007), which is now a very powerful tool for SharePoint data management.

To 'link' to a SharePoint list, start a new MS Access database. Then click the "External Data" tab and select "SharePoint List" from "Import" section (not the Export section).

Enter the URL of the site where the list is that you want to connect to and select the "Link to the data source by creating a linked table" option. Then click "Next".

Check the list(s) and/or libraries you would like to link to. Then click "OK".

Reviewing the Access Objects

When the link is created, you will see your list(s) and an unexpected object called "User Information List". This is needed, because SharePoint stores user information in a special table. The list content only stores the "ID" of the user. The connection wizard automatically takes a copy of the needed users, and makes an automatic lookup reference to the the user information.

Below shows both the SharePoint list (top) and the new linked table in MS Access (bottom). The data matches perfectly.

When I change the data in MS Access (below), the SharePoint data gets immediately updated.

This is a very powerful resource for a SharePoint administrator. Think of the possibilities!

Next Steps
  • Look for future tips on using MS Access to migrate SharePoint content between instances and even versions.
  • Check out MSSQLTips.com for great information about Microsoft SQL Server.


Last Updated: 2010-04-20


get scripts

next tip button



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips





Comments For This Article





download





Recommended Reading

Reading SharePoint Lists with Integration Services 2017

Using SSIS to Automatically Populate a SharePoint List

Understanding SharePoint Exports to Excel

PerformancePoint Dashboard - a Primer

How to apply Microsoft SharePoint 2013 Cumulative Updates and Handling Issues








get free sql tips
agree to terms


Learn more about SQL Server tools