Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

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

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

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