Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 15.1 Work with SharePoint Data from Access

15.1.1 Problem

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?

15.1.2 Solution

This solution requires Access 2003 and a web server running Windows SharePoint Services.

Access 2003 supports the linking to (and importing of) SharePoint lists and document libraries. To link to a SharePoint data source, follow these steps:

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 listbox.

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 lists.
Click Finish to complete the link process and create the linked tables.

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

15.1.3 Discussion

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.

[ Team LiB ]