Office Access 2007 has many new features that support working with Windows SharePoint Services lists as linked tables. Access 2007 forms, reports, queries, and code can all work with data that is contained in SharePoint lists as easily as with data contained in standard database tables. Because Windows SharePoint Services exposes all workflow-related information in lists, Access also makes it easy for information workers to create rich interfaces to this workflow data.
Access applications can use SharePoint lists for storage of application data. By associating workflows with these lists, you can create server-side logic that runs reliably whether users edit the data in Access forms or in SharePoint pages that are viewed in a browser.
With Access 2007, you can specify that you want to create and link to new SharePoint lists in an existing site when you create a new application by using one of the Access templates. One of the greatest strengths of Windows SharePoint Services is how easily it enables users to create new sites. After you create a site, you can populate it with lists that will hold your Access data. The lower-right corner of Figure 11 shows how you can specify in Access that a new application should use a Windows SharePoint Services site for storage.
Figure 11. Creating a new application that stores data in SharePoint lists
You can also choose to save a copy of your application in a Windows SharePoint Services document library, enabling users to download the latest version automatically. This integration with document libraries provides an easy way to deploy your application. A dialog box lets you specify the site where your data lists will be created and also to specify a document library where your application should be made available. Figure 12 shows the lists in the new site. The lists that show that they were modified "7 minutes ago" are standard lists that were added to the site automatically when it was created. Those that were modified "2 minutes ago" are the lists that Access created and linked to from the new application. Using SharePoint lists for data storage provides several benefits. For example, every list maintains data about when and by whom each item was last modified. You can also create an automatic audit trail showing all the changes.
Figure 12. Viewing lists in the Windows SharePoint Services site created by Access
With your application data stored in Windows SharePoint Services lists, you can select and configure workflows to be applied to these lists by following the steps for adding workflows to a document library. If you are using Office SharePoint Server 2007, users can even initiate workflows manually from Access 2007. If you are using the version of Windows SharePoint Services that is included with Microsoft Windows Server 2003, users must use the browser for manual initiation of a workflow. In both cases, however, you can configure a workflow to begin automatically when a new item is added to a list (which appears in Access as a new row being added to a table), or when an item is modified.
The built-in workflows are useful for common tasks, especially tasks that are often required for documents such as review and approval. However, if you are using lists to store data for a business application, you will probably need to create custom workflows that incorporate your specific business rules.
In many cases, you can achieve the workflow customization that you need without using Microsoft Visual Studio and without writing any Microsoft .NET Framework-based code, by using Office SharePoint Designer 2007. This program, which is the successor to FrontPage 2003, is useful for many tasks related to designing and customizing SharePoint sites, including creation of custom Sequential workflows.
After you open your site in Office SharePoint Designer, right-click the site in the Folder List, select New, and then select SharePoint Content, as shown in Figure 13.
Figure 13. Creating new content in Office SharePoint Designer
In the dialog box, select the type of content that you want to create (in this case, a workflow, as shown in Figure 14).
Figure 14. Selecting the type of content to create
Next, you see the Workflow Designer wizard, which begins by asking for much of the same information requested when you create a workflow in a Windows SharePoint Services site, as shown in Figure 15.
Figure 15. Defining a new workflow
The Workflow Designer wizard enables you to build a workflow by configuring a series of steps, each containing Conditions and Actions. The user interface is similar to one you might be familiar with from creating rules in Outlook. It insulates you from the details of the underlying WF Activity objects while still providing access to a variety of workflow features. The wizard makes it easy to bind parameters to values from the underlying list item or to look up items in other related lists in the site.
Figure 16 shows the available condition types, and a condition that was created using the Custom Condition type to bind to a value in the current item in the Projects list.
Figure 16. Creating a condition based on a value from the current list item
Figure 17 shows how the wizard makes it easy to select column-specific values for conditions.
Figure 17. Selecting column-specific values
When you click Actions, you see a partial list of available actions. By selecting More Actions, you can view the full list of available actions, shown in Figure 18. This also shows how you can use a Do Calculation action to perform a calculation and store the result in a local variable.
Figure 18. Viewing the full list of available actions
Each action has its own supporting user interfaces for supplying appropriate parameter values. Figure 19 shows how you can define the message that should be sent by an E-mail action and how the message can include values from the current list item.
Figure 19. Configuring the message to be sent by an E-mail action
Although the type of workflow model you create in this designer is always a Sequential workflow, not a State Machine workflow, you are not limited to strictly linear execution flows. Figure 20 shows two parallel branches, and it also shows how you can create a new local variable.
Figure 20. Adding a local variable to a workflow with parallel branches
After you add a workflow, it appears in a new Workflows folder in the Folder List in the SharePoint Designer, as shown in Figure 21. This figure also shows the XOML files that are used to capture the choices you make when configuring a workflow. Because no new code is created, you can deploy your workflow to the site without having to be a server administrator.
Figure 21. Viewing XOML files for a workflow created in SharePoint Designer 2007
In addition to using workflows to enhance your Access 2007 applications, you can also use Access to enhance your workflow reporting. Office SharePoint Server 2007 includes several built-in reports that use Excel 2007 worksheets, as shown in Figure 22.
Figure 22. Using built-in workflow reports
One major limitation of the built-in reports is that they can only contain data from a single site. Sometimes information workers want to see reports that span several sites or that include data from other related data sources, such as line-of-business databases. Access has always been a great tool for creating distributed queries that span multiple data sources, and forms and reports based on these queries.
In Access, you can easily link to lists from a variety of Windows SharePoint Services sites, including the task and history lists that are used for implementing and tracking workflows. To link to an existing SharePoint list in Access, select External Data, and then click SharePoint List to open the Get External Data dialog box. Here you can specify the site to link to, and select one or more lists from that site.
Often the workflow-related lists in multiple sites have the same names. For example, all the workflows may use the standard Tasks list. After you create a linked table in Access, you can right-click the table to rename it. Figure 23 shows several linked tables that have been renamed to distinguish which sites they are from and a union query that combines workflow data from two different sites.
Figure 23. Querying data from multiple workflows
Figure 24 shows part of a report that uses this query and that groups the data by user and by workflow.
Figure 24. Grouping data by user and by workflow in a report