Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: > 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 Update:






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





More SQL Server Solutions











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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools