Access 2003 and Windows SharePoint Services

  • Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Mike Gunderloy

After a decade of 32-bit development, you might think that Microsoft Office was finished. Despite that, Microsoft is still finding new things to put in to the Office box. In Office 2003, there's a renewed emphasis on collaborative features, centering around the newly renamed Windows SharePoint Services. In this article, Mike Gunderloy shows you how Access 2003 can work with data stored in SharePoint.

SharePoint has been around for a few years now, in two separate versions: a workgroup product originally known as SharePoint Team Services, and a portal product known as SharePoint Portal Server. Though these products share a name, they were in fact separate code bases with the same goal: allowing teams and workgroups to share documents and other collaborative information using Web protocols.

This year, though, SharePoint is moving from the wings to center stage. It's been re-architected to use SQL Server and ASP.NET as its underpinnings, and integrated much more closely with other Microsoft software products, including Office 2003. This integration extends to Access 2003, which is able to treat SharePoint as another data source for importing, exporting, and linking data.

Introduction to Windows SharePoint Services 2.0

Windows SharePoint Services (formerly SharePoint Team Services) is Microsoft's solution for workgroup and department collaboration. SharePoint Services allows you to communicate, share documents, and work together on projects using nothing more than a Web browser—or any Microsoft Office 2003 application.

To make use of SharePoint in Access 2003, you'll need to install the new version of SharePoint Services. While there are ways to extract data from the original release of SharePoint to Access 2003, the deep integration possible with Access 2003 requires using version 2.0 of SharePoint Services. You'll need to have a server meeting these minimum prerequisites:

  • Windows Server 2003 operating system
  • NTFS file system
  • IIS 6.0 installed in its default Worker Process Isolation Mode
  • ASP.NET installed

If you've met these prerequisites, installing SharePoint Services is easy. Just run the SETUPSTS.EXE file on the Windows SharePoint Services CD-ROM. This installs SharePoint Services on the default Web site for the computer, and sets up SharePoint's own instance of the Microsoft Desktop Engine (MSDE) version of Microsoft SQL Server (this instance will store the SharePoint Services data). For this article, I used the Beta 2 Technical Refresh versions of SharePoint Services and Access 2003.

Figure 1 shows a SharePoint 2.0 site open in Internet Explorer. While this is the default way to interact with SharePoint data, it's far from the only way. In particular, Access 2003 sees SharePoint as a first-class data source in its own right. Note the "Lists" category in the navigation bar to the left of the window. A SharePoint list is the equivalent of an Access table, being a rectangular array of data with fields and records.

Exporting, importing, and linking data

While Web browsers do provide ubiquitous access to SharePoint data, they hardly provide the best possible user experience. Of course, as Access developers we already know an application that's designed for a top-notch user experience when browsing structured data: Access. So it's good to know that Access 2003 lets you treat SharePoint Services much like any other data source. You can export Access data to SharePoint, import SharePoint data to Access, or (perhaps most interesting from the standpoint of collaborative applications) link SharePoint data to Access and access the data in real time. I'll demonstrate each of these fundamental operations.

First, a warning. I'm using the Beta version of Access 2003, so some details may change by the final release.

****

Exporting data from Access to SharePoint

To start with, here's how you might export a table from Access to SharePoint:

  1. Right-click on a table in the database window and select Export.
  2. Select SharePoint Team Services in the Save As Type combo box (presumably this option will change to "Windows SharePoint Services" in the final release of Access 2003). This will launch the Export to Windows SharePoint Services Wizard.
  3. Enter the URL of the SharePoint site where the exported data should reside. You can also specify a list name and description; by default, these will be filled in with the Access table name and description. Click Finish.

Figure 2 shows the exported table as a SharePoint list. As you can see, SharePoint exposes a reasonably rich user interface for working with list data (rich for a Web application, at any rate).

****

Importing SharePoint data to Access

Of course, if you can export, you can import. Importing SharePoint data to Access is a bit more complicated than exporting it, but still relatively straightforward:

  1. Select File | Get External Data | Import.
  2. Select SharePoint Team Services in the Files of Type combo box (once again, I expect this will change for the release version). This will launch the Import From Windows SharePoint Services Wizard.
  3. Enter the URL of the SharePoint site containing the data to import and click Next.
  4. On the next panel of the wizard, you can choose whether to import lists or views. A view is similar to an Access query: It represents some subset of a list (for example, "My tasks" is a view of the standard Tasks list). You can use Ctrl+Click to select multiple lists to import.
  5. You also need to select what to do about lookup columns. The check box "Retrieve IDs for lookup columns" is somewhat confusing. If you uncheck the box, then any lookup columns in the data are filled with data from the other list. For example, the Tasks list has some lookup columns that get names from the UserInfo list. If you uncheck this box, then the imported Access table will contain the actual names, rather than the numeric IDs. If you check the box, the numeric IDs will be imported.
  6. If you choose to retrieve IDs, the wizard will next let you decide what to do about the related lists. You can choose to create linked tables pointing to those lists, or to import them to their own Access tables.
  7. The final pane of the wizard will confirm your choices. Click Finish to import the table or tables that you chose.

When you import SharePoint data to Access, Access will import as many field properties as it can. For example, if a SharePoint list contains a currency column, the import process will create a currency field in Access. Access will also inherit the Required, Default value, Format, and Decimal Places settings from the SharePoint column (remember that it's MSDE/SQL Server that's storing the SharePoint data, so there's a lot of overlap with Jet tables). In addition, Access will build a validation rule to enforce the minimum and maximum values set in SharePoint.

****

Linking SharePoint data to Access

Finally, you can link SharePoint data to Access. This has the usual benefit of linked data: It lets you keep the data stored in SharePoint, and still work with it in Access. Linking works just like importing, except that you can only link (not import) related tables. Access uses a stylized calendar icon to represent a list linked from SharePoint. Figure 3 shows the database window with two linked SharePoint lists.

Microsoft supports editing data simultaneously in Access and SharePoint with a pessimistic locking scheme. If users change the same record from both environments at the same time, the second copy to be saved will trigger an error message. On the SharePoint side, the error appears as a Web page telling the user that someone else has modified the item, and offering the chance to save their data over the top of the modifications. On the Access side, users get the familiar Write Conflict dialog box if they try to overwrite changes made by another user.

The upshot of this coordination is that in some circumstances SharePoint lists offer an ideal way to extend the reach of an Access database to work with users who don't have Access. For example, suppose you have field representatives who need to be able to see a parts table that's been stored in an Access database. Rather than write your own Web interface for the data, you could export the table to a SharePoint site on a server with a public Internet connection and then link the list back to your Access database.

Programming SharePoint access

Traditionally, one of the strengths of Access has been that everything you could do from the user interface could also be done from VBA code. That's still true when working with SharePoint but, as I'll show you in a moment, there's one little complication.

As with other data transfer operations, you use the DoCmd.TransferDatabase method to move data to and from SharePoint programmatically. To export a table named Orders to a SharePoint list named OrdersList, you can use a command like this:

DoCmd.TransferDatabase acExport, "WSS". _
 "http://Server/sites/testsite", acTable, "Orders", _
 "OrdersList", False

The final parameter controls whether to export only the structure of the structure, or export both the structure and the data from the table.

To import or link a SharePoint list, you can also use the TransferDatabase method—but this is where the complication comes in. Here's a VBA command to link a SharePoint list named Parts:

DoCmd.TransferDatabase acLink, "WSS", _
 "WSS;HDR=NO;IMEX=2;" & _
 "DATABASE=http://Server/sites/testsite;" & _
 "LIST={800BE2B7-FA3C-4CFC-BBB3-8500C4EDCF22};" & _
 "VIEW=;RetrieveIds=Yes;TABLE=Parts", acTable, , _
 "Parts"

Ugly, isn't it? The problem is that SharePoint doesn't refer to lists by name. Internally SharePoint uses GUIDs to identify lists (that's how it can store, for example, 10 different Contacts lists in the same MSDE database). When you use the Access user interface to import or link SharePoint data, Access takes care of matching the names to the GUIDs. But when you're dealing with the Jet engine directly through VBA, you're on your own. The long string (starting with "WSS" and ending with "TABLE=Parts") identifies a particular SharePoint list.

If you're faced with the problem of programmatically importing or linking SharePoint data, there are two ways to get the correct GUID. The easy way is to manually link the desired list to an Access database, and then open the linked table in design view—the Description property of the linked table will contain the necessary linking string. Obviously this is less than useful when you need to be able to link to arbitrary lists, though. In that case, you can use the Web Service interface to SharePoint, which exposes a Lists.GetList method. This method takes the name of a list on a particular site and returns detailed information, including the GUID that identifies the list.

The future of Office?

The fact that Microsoft is making Windows SharePoint Services a core part of Windows Server 2003 suggests just how seriously Microsoft is taking collaboration these days. In part, this grows out of Microsoft's own experience as a worldwide company pursuing large collaborative projects via an intranet. They're betting that other corporations, too, will find pervasive collaboration to be a good reason to upgrade to the new version when Office 2003 ships. In addition to lists, SharePoint is also a repository for documents and threaded discussions. Word and Excel have built-in links that take advantage of these SharePoint services directly from their own user interface (expect to see this functionality incorporated into a future version of Access as well). If SharePoint becomes your corporate repository for collaborative data, and Access can interact with that data directly, then it's entirely possible that Access will become a key component of corporate strategy. And that's a nice thing for Access developers to think about.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the September 2003 issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.