Recipe 15.1 Work with SharePoint Data from Access
Windows SharePoint Services makes it easy to
create collaborative, team-based
web sites consisting of various lists and documents. Is it possible
to use Access as a frontend to SharePoint data?
This solution requires Access 2003 and a web server running Windows
Access 2003 supports the linking to (and importing of) SharePoint
lists and document libraries. To link to a
SharePoint data source, follow
Select File Get External Data Link Tables... to
open the Link dialog box. Under the Files of Type dropdown control,
select Windows SharePoint Services (WSS). Access starts the Link to
Windows SharePoint Services wizard as shown in Figure 15-1.
Figure 15-1. Selecting the SharePoint site to which you wish to connect on the first page of the Link to Windows SharePoint Services wizard
Enter the URL of a Windows SharePoint Services (WSS) site into the
site textbox and click Next. At this point you may be prompted to
login to the site.
At the second page of the wizard you will be prompted to select a
list as shown in Figure 15-2. Some SharePoint lists,
such as the Events and Tasks lists, provide multiple views of the
list. If you wish to link to each of these views, then select
"Link to one or more views of a
list" and select the list from the Lists listbox.
Otherwise, if you wish to link to several lists, select
"Link to one or more lists" and
hold down the SHIFT or CTRL key to select multiple lists in the Lists
Figure 15-2. Selecting the SharePoint lists on the second page of the wizard
If you checked "Retrieve IDs for lookup
columns," then the next page of the wizard will
present a set of related lists that you will need to include in order
to update the lists. You can deselect the related lists at this
point, but if you do you will be unable to update data in the linked
Click Finish to complete the link process and create the linked
Once you've linked to a list you can open the list
within Access to view or edit existing list items or create new
items. You can, of course, also create queries, forms, and reports
based on the linked lists.
Figure 15-3 shows a linked list in datasheet view.
You can modify data directly in Access or click on a
row's Edit hyperlink to edit the row using a
SharePoint web page.
Figure 15-3. The Announcements list in datasheet view
You can use Access to create various reports on usage of your
SharePoint site. Link to each of the SharePoint lists and create
reports based on the linked lists. This way there is no need to
master some other reporting package; use the reporting tool with
which you are most comfortable: Access.
You can also import SharePoint list data into an Access database by
selecting File Get External Data Import...
Versions of Access prior to Access 2003 cannot be used to link to
SharePoint lists. In addition, you can only link to sites using
Windows SharePoint Services 2.0 or later.