Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

By:   |   Last Updated: 2010-04-20   |   Comments   |   Related Tips: > Sharepoint


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?


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

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools