Special Edition Using Microsoft Office Access 2003

Windows SharePoint Services (WSS) is a workgroup portal server that uses Internet Information Services 6.0 to manage and display content and MSDE or SQL Server 2000 as its data store. WSS is intended for sharing documents, lists, InfoPath forms, Web pages, and images among portal users. WSS also offers newsgroup-style discussion boards, instant messaging, and survey features. Unlike its more powerful relative, SharePoint Portal Server, WSS doesn't require client licenses for users.

WSS is an add-in to Windows Server 2003 that you access with IE 5.0+. (Using browsers other than IE 5.0+ results in reduced functionality.) The examples of the following sections require that you have access to a WSS site on a local development computer, your organization's intranet, or from a third-party WSS hosting facility. You also need administrative privileges for the site. Limited experience administering a WSS site is helpful but not required.

Tip

Apptix On-Demand hosts WSS services as an application service provider (ASP). You can learn more about this program at http://www.apptix.net/sharepoint.htm.

Note

The following sections' examples run WSS in a development environment. WSS is installed as the default Web site at http://oakleaf-w2k3/. OakLeaf-W2K3 is a Windows 2003 member server in the OAKLEAF Windows 2000 domain. Office System 2003 and InfoPath 2003 are installed on the member server, so http://localhost/ opens the WSS home page in IE. If you have a different configuration, you might need to change the URL, security settings, or file paths to achieve the expected results.

Exporting Tables or Queries to a SharePoint List

The simplest interaction with WSS is exporting a Jet or SQL Server table or query as a WSS list. In this case, there is no connection between the list and your Jet or SQL Server database.

To export a table, such as the Northwind.mdb or NorthwindCS Customers table to the default WSS site, do the following:

1. Launch Access 2003 and select the Customers table in the Database window.

2. Right-click the table entry, choose Export, and select SharePoint Team Services in the Files of Type list to open the Export SharePoint Team Services Wizard.

3. Complete the URL to your WSS site (http://localhost/ for this example), and modify the default list name and description, if you want (see Figure 26.42).

   Figure 26.42. Type the URL for your WSS site and give the list a name and description in the first Wizard dialog.

4. With the Open This List When Finished check box marked, click Finish to export the list. After you acknowledge the "Finished exporting table 'listname' to 'URL'" message, the list opens in WSS datasheet view (see Figure 26.43).

   Figure 26.43. The exported table appears as a list in WSS's data sheet view for users who have Office 2003 installed.

   Note

   Users who don't have Office System 2003 installed see the list in standard view, because datasheet view requires Office Web Components 11.0 on the client. You can add a new item but not edit existing entries in standard view.

5. Right-click a column to display a context menu of operations you can perform on the column. For example, choosing Edit/Delete Column opens the Edit Column page that lets you add a default value, specify formatting for the data type, require a value for new items, and set the maximum number of characters.

6. Open a column's drop-down list to sort or add a filter in datasheet view. You can select Sort Ascending or Sort Descending, and then select an individual value or up to three custom sort conditions in the Custom Filter dialog (see Figure 26.44).

   Figure 26.44. The Custom Filter dialog lets you apply up to three WHERE clause criteria to the list.

7. Open the list and select (Show All) to remove the filter. You can't remove the sort in this list; you're only choice is to sort on another column, such as the primary key (CustomerID).

8. Click the Task Pane button and click the Sort link to open Custom Sort dialog that lets you sort the list by a sequence of up to three columns. You can remove the sort you applied in the preceding step by opening the drop-down list and selecting the empty choice.

9. Click New Row to move to the tentative append record at the end of the list. Like Access, you don't add a record until you type values for at least the required fields.

Linking a WSS List to an Access Table

The most common reason to export Access data to WSS is to link a Jet table to the list. Linking makes edits to the table in Access or the list in WSS visible in both applications. This capability lets you establish relationships between the list and other tables, and use Access forms and VBA code to maintain the list.

Note

You can't create a link from WSS to the SQL Server database of an Access Data Project. You receive a "Links can only be created between Microsoft Access database files" error message if you try.

To link a new Jet table to a WSS list using the Customers list you created in the preceding section as an example, do the following:

1. Click the Task Pane button to open the task pane and scroll down to expose the three Access options.

2. Click the Create Linked Table in Access link to open the Export dialog. Accept the default Existing Database option and click OK to display the Open dialog.

3. Navigate to Northwind.mdb or NorthwindCS.adp and double-click the filename to launch Access 2003 and add the link to the list's underlying MSDE database (see Figure 26.45). The table name is the list name with a colon-separated view name; by default, the view is All Items.

   Figure 26.45. A combination of the list and view names creates the linked table's name.

   Tip

   Rename the link to simplify Access references to the table for example, CustomersList. Renaming the link doesn't affect the connection to the list.

4. Open the linked table in Access. Linking to a list adds an AutoNumber column for row-level tracking.

5. Add a temporary new record to the Access table, BOGUS and Bogus Company for this example.

6. Return to the WSS list, click the Refresh Data button to display the added record at the end of the list. You might need to reapply the sort instruction to the sorted column once or twice to reposition the new item to the correct location (see Figure 26.46).

   Figure 26.46. Inserting a new temporary record in Access adds the record to the end of the WSS list when you click Refresh Data. Reapply the sort to make the record appear at its correct position in the list.

7. Select the added record, press delete, and confirm the deletion to remove it from the list.

8. In Access, close and reopen the table to verify that the record has been deleted in the linked table. Choosing Records, Refresh doesn't indicate that the record has been deleted.

9. Verify updates propagate from the list to Access by clicking New Record, adding a temporary new record, and opening the table in Access.

10. Delete the record in Access, return to WSS, click Refresh Records, and verify the temporary record is gone.

Linking an Access Table to a WSS List

An alternative to creating a link from WSS to an Access table or query is using Access's linked Jet tables feature to create a link to the data underlying the WSS list. You can't create a usable link to WSS's named MSDE instance COMPUTERNAME\SharePoint. The table linked from Access to a WSS list has a different structure than that from a link you establish in the reverse direction.

To export another WSS list and link to it in Access 2003, do the following:

1. Open Access and export another Jet or SQL Server table, such as Suppliers to your SBS site. (Refer to the earlier "Exporting Tables or Queries to a SharePoint List" section for the detailed steps.)

2. If you export the Jet Suppliers table, check the HomePage column of the list to confirm that WSS supports the Jet Hyperlink field data type.

3. Return to Access and choose File, Get External Data, Link Tables to open the Link dialog.

4. Choose SharePoint Team Services in the Files of Type list to open the first Link SharePoint Team Services Wizard. Select or type the URL to your WSS site, and click Next to open the Select Lists dialog.

5. Select the list to link, Northwind Suppliers for this example, select the Link to One or More Lists option, and clear the Retrieve IDs for Lookup Columns, because this list has no lookup columns (see Figure 26.47).

   Figure 26.47. Access's Link SharePoint Team Services Wizard lets you create one or more links to every list in your WSS site.

6. Click Finish to create the link. When you open the link, it's obvious that linking from Access to WSS generates a table structure that differs dramatically from that created by linking from WSS to Access (see Figure 26.48).

   Figure 26.48. Linking a Jet table to a WSS list from Access adds list management fields to the table. These fields don't appear when you create the link from WSS.

7. Click a hyperlink in the Edit column to open a WSS editing form for the record you select. A red asterisk indicates a required field (see Figure 26.49).

   Figure 26.49. Linking from Access creates a hyperlink to WSS that lets you edit the record in an WSS form. You also can edit the list in Access's Datasheet view.

8. Test the ability of Access to add a new record to the list by clicking the new record button, typing the next SupplierID value, and adding at least the required CompanyName value.

9. Move the record pointer to add an Edit hyperlink to the record. You can edit the record either in Access or in the WSS form.

10. Return to WSS, open the Northwind Suppliers list, if necessary, click Refresh Data, and verify presence of the record at the end of the list.

11. Delete the record. As was the case in the preceding example, you must close and reopen the table to verify deletion.