Integrating Workflows into Access 2007 Applications

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn about the new capabilities provided by Office SharePoint Server 2007 and Windows Workflow Foundation and how you can integrate workflows with your Access 2007 applications. (12 printed pages)

Andy Baron, MCW Technologies

May 2007

Applies to: 2007 Microsoft Office System, Microsoft Windows Workflow Foundation, Microsoft Office SharePoint Server 2007

Contents

• Overview of Workflows in SharePoint Server 2007 and Access 2007

• Windows Workflow Foundation Architecture

• Windows Workflow Foundation in the 2007 Microsoft Office System

• Integrating Access 2007 with Workflows

• Conclusion

• Additional Resources

• About the Author

Overview of Workflows in SharePoint Server 2007 and Access 2007

Software applications often must model a flow of work activities. For this reason, flow charts are a common tool for designing and representing application requirements. However, the implementation of these workflows and the reports that track their progress usually rely on custom code that is highly specialized and tightly woven into the fabric of each application. Often the workflow rules are mixed in with validation code or with other business rules, and modifying business workflows after an application is deployed usually requires modifying the application code.

Microsoft Windows Workflow Foundation (WF) represents an ambitious effort to provide an extensible framework and a set of tools to meet the varied workflow needs of Windows programmers. Microsoft recognizes that the requirements for workflow support are just as universal as the need for frameworks to support such things as data access, presentation, and communication, even though workflow support is rarely built into application-development platforms. The inclusion of WF in the Microsoft .NET Framework 3.0 gives developers one more reason to adopt Microsoft technologies.

Microsoft Office SharePoint Server 2007 hosts Windows Workflow Foundation to provide new functionality. It is easy to associate standard workflows with document libraries to manage and track review, approval, expiration, and other common document life-cycle tasks. Microsoft Windows SharePoint Services 3.0, on which Office SharePoint Server is built, also enables workflows to be associated with any SharePoint list.

Microsoft Office Access 2007 developers can associate workflows with SharePoint lists that are used in their applications as linked tables. They can also create reports that are linked to the lists that SharePoint Server uses for tracking workflows. Microsoft Office SharePoint Designer 2007 enables solution builders to configure custom workflows without writing code. With Microsoft Visual Studio 2005 Extensions for Windows Workflow Foundation, programmers can create new workflow activities and workflow models.

Windows Workflow Foundation Architecture

The WF architecture is impressively open. It is intended to support a wide variety of workflow scenarios, running within a wide variety of applications, from a small single-user sequential workflow running in a desktop application to a complex, mission critical state-machine workflow distributed across multiple enterprise servers. In these varied applications, the number of activities, the workflow models, and the tools that are required for implementation and testing differ significantly, but they all can be built by using the same WF framework.

Activities

Activities form the fundamental building blocks of all WF workflows. Activities can be simple or composite. Each workflow is a composite activity that is composed of child activities. Workflow activities are all derived from base classes that are provided by the WF framework. Each activity supports a set of parameters for configuring how it should run, a set of conditions that determine when it should run, and a set of events that are raised when it does run.

Developers can build their own activities by extending existing activities, or they can rely on out-of-the-box activities that are part of the WF framework. Microsoft expects that a rich ecosystem will evolve to give developers a large selection of activities, including some freely shared within communities, some developed by corporations for internal use, and some sold by independent software vendors (ISVs) or as part of broader solutions. Some activities are intended to support system workflows in which multiple software applications interact with each other, and some activities support human workflows in which people interact with applications.

The activities that the framework provides include activities for controlling the flow of execution, such as IfElse, While, Sequence, and Delay. In addition, there are activities for updating data associated with the workflow, for handling and throwing exceptions, for calling Web services and invoking object methods, and even a Code activity that runs any designated block of code.

Workflow Engine Hosting

Although WF includes a runtime engine for processing workflow activities, this engine does not run as a stand-alone executable. Instead, it is always hosted by another application that provides essential services by implementing interfaces that WF defines. As part of the Windows Workflow Foundation SDK, Microsoft includes prebuilt host implementations for console, Windows Forms, and ASP.NET applications. Windows SharePoint Services also hosts WF. So most developers, other than system developers working for ISVs, do not have to write their own WF hosts. However, it is useful to understand the services that a host must provide.

Here is a brief overview of the primary services:

• Persistence   Workflows often run for days, weeks, or even years before they are completed. For example, a document expiration workflow might be initiated when the document is created, and then raise an expiration event several years later when it is time for the document to be archived. It would be impractical to expect all active workflows to remain in memory throughout their lifetimes. So a persistence service must be able to serialize, or "dehydrate," a running workflow to some type of persistent storage, and then "rehydrate" the workflow when an event signals that its processing should continue. When available, a database server, such as SQL Server, is often used for persistence, but you could also use XML documents or other file-based mechanisms.

• Timer   Many workflow activities involve waiting for a set period of time. The document expiration workflow that requires persistence while waiting for the expiration period to elapse also requires a timer of some kind to determine when the expiration is due. On a smaller scale, an approval workflow might need to send a reminder or perform some other form of escalation if approval is not completed within a specified time period.

• Tracking   Persistence is not only used to store the state of active workflows, it is also used to persist tracking information that records the progress of a workflow from initiation to completion. This provides visibility into the state of active workflows and a history of what has occurred.

• Communication   This service handles communication between the workflow engine and the host. It is left to the host to determine what type of messaging system to use.

• Threading   Because WF is always hosted within another application’s process, it also relies on that application to manage threading.

Workflow Models

Each running workflow is an instance of a workflow model, which is often referred to as "a workflow." WF supports two out-of-the-box workflow models: Sequential and State Machine. These two workflow models can be extended or supplemented by additional models that are created by workflow developers.

As the name suggests, a Sequential workflow consists of a series of defined steps. The sequence does not have to be linear. Sequential models can include loops and parallel paths, but movement from one activity to another is defined by a set of rules that is embodied in the workflow. This is similar to the way that procedural code execution is controlled by logic that is embodied in the code.

State Machine workflow models are more suited to applications where the decisions about how the application proceeds are made externally. Instead of defining a limited set of transitions and rules for determining when each transition will occur, a State Machine model defines a set of possible states for the workflow and a set of actions that are allowed in each state to move the workflow to a different state. These workflows leave open the number of execution paths that are possible and are more suitable in applications where unpredictable external logic determines the path followed in any particular instance.

State Machine workflows support only a small subset of activities. State activities can contain child activities for initializing and finalizing state, and for handling events while in each state. The event handler activities can include a child activity for changing state.

Developers or development tools can create workflow models either by generating XML documents that use a grammar called XOML (Extensible Object Markup Language), which is similar to the XAML (Extensible Application Markup Language) that is used with Windows Presentation Foundation, or they can create and configure workflow instances in code.

Workflow Tools

In addition to including a hosted execution engine and an extensible class library, WF includes tools. Visual Studio 2005 Extensions for Windows Workflow Foundation provides several project templates for creating new activity and workflow class libraries, plus a graphical designer for building workflow models. Figure 1 shows a set of workflow activities in the Visual Studio Toolbox, and two designer windows displaying graphical representations of Sequential and State Machine models.

Figure 1. Creating workflow models in Visual Studio

The graphical designer shown in Figure 1 is not limited to being used in Visual Studio. It is a component that can be hosted in any application that needs to enable users to create and modify workflows. In addition, Office SharePoint Designer 2007 includes a graphical wizard for building and configuring no-code Sequential workflows for use with Windows SharePoint Services.

Windows Workflow Foundation in the 2007 Microsoft Office System

The architects of the 2007 Microsoft Office system recognized that WF could help meet their needs for workflows supporting document review, approval, publishing, versioning, expiration, and other common life-cycle tasks. They chose to host WF in Windows SharePoint Services, and to add value to the Microsoft Office SharePoint Server 2007 by providing out-of-the-box workflows to support document management and integration with the client-side Office applications.

The document-creation applications in the 2007 Microsoft Office system, including Microsoft Office Word 2007, Microsoft Office Excel 2007, and Microsoft Office PowerPoint 2007, provide support for initiating document workflows from within the applications and for viewing and editing workflow tasks related to the current document. This supplements the browser-based support provided by document libraries and other lists in Office SharePoint Server. In addition, you can use Microsoft Office InfoPath 2007 forms to set the parameters that are used to configure workflows, and Microsoft Office Outlook 2007 provides support for editing workflow tasks that are contained in e-mail notifications.

Creating and Running a Document Workflow

To associate one of the built-in workflows with an Office SharePoint Server document library, begin by selecting Document Library Settings, as shown in Figure 2. To perform this task, you must be an administrator of the site or of the document library list.

Figure 2. Selecting document library settings

Next, click Workflow Settings, as shown in Figure 3.

Figure 3. Selecting workflow settings

On the Change Workflow Settings page, click Add a workflow to open the Add a Workflow page shown in Figure 4.

Figure 4. Adding a new workflow

On this page, you select the type of workflow you want from the list of available workflow models. This list includes the built-in workflows and any custom workflows that have been deployed to the server by a server administrator, enabled by a site collection administrator, and associated with that document library list by a list administrator. Only workflows that contain new code require a server administrator for deployment. Workflows that configure existing activities, such as those that are created in the SharePoint Designer, can be deployed by a site collection administrator.

In addition to specifying the type of workflow to associate with the document library, you must name the workflow and select a task list and a history list to be used by the workflow. You can also specify how the workflow can be initiated. Workflows can be started manually by users, or they can start automatically when an item is created or modified.

Configuring Workflows

After you click Next on the Add a Workflow page, you see a form that is specific to the selected type of workflow. The page shown in Figure 5 displays some of the parameters that must be set for a Collect Feedback workflow, which in this example is named "Review." These parameters are defined by the developer who creates the workflow model and can be accessed by the workflow code during execution.

Figure 5. Adding and customizing a Collect Feedback workflow

Starting a Workflow Manually

When a workflow is configured and associated with a document library, any document that is added to the library can trigger creation of a new instance of that workflow model, depending on the start options that were selected for the workflow. If the workflow is configured to allow only manual initiation, it is up to a user to start the workflow. In Office Word 2007, you can click the Microsoft Office Button in the upper-left corner of the application, and then select Start Workflow. A dialog box appears and displays all the available workflows for the document library containing that document. You can then select which one to start.

Viewing Available Workflows

You can also use the browser interface to a document library to view the available workflows, as shown in Figure 6, and to start any workflows that support manual initiations, as shown in Figure 7. If a workflow is configured to start automatically when a document is added to the library or is modified, no user action is required.

Figure 6. Viewing available workflows for a document

Figure 7. Initiating a document workflow manually from a browser

When a workflow is associated with a document library, a column is added to the document library list with the name of the workflow. You can view the detailed status of the workflow by clicking the status value that appears in this column, as shown in Figure 8.

Figure 8. Viewing the detailed status of a document workflow

The status page displays the items in the workflow’s Tasks and History lists. When you want to edit or complete a workflow task that has been assigned to you, you can click the title in the task list and enter data in a form such as the one shown in Figure 9. E-mail notifications that are sent when tasks are assigned include links to this page.

Figure 9. Completing a workflow task

Figure 10 shows the listing for a task that has been completed.

Figure 10. Viewing a completed task listing

This example shows how you can associate a workflow model with a document library list, and then work with the workflow instance that is created for each item in the list. However, workflows are not limited to use with document libraries. Any Windows SharePoint Services list can have one or more workflows associated with it.

Integrating Access 2007 with Workflows

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.

Linking Access Tables to SharePoint Lists

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.

Creating Workflows Using SharePoint Designer 2007

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

Using Access 2007 to Query Across Workflows

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

Conclusion

All developers wrestle with the need to enforce workflow rules in their applications, usually with code that provides little reuse among different applications. Shared class libraries are often used for the business logic invoked by individual workflow steps in multiple different applications, but the code to handle the progression of steps might be unique to each application. In addition, modifying these workflows can require digging deep into the application code.

Windows Workflow Foundation simplifies many of the programming tasks required to implement workflows, while still giving programmers the freedom to define whatever custom workflow activities are needed. Workflows that are created by using WF can often be reconfigured without requiring code modifications. Visual Studio 2005 Extensions for Windows Workflow Foundation provides project templates for creating new workflow models and activities, and a graphical designer that simplifies common tasks.

The 2007 Microsoft Office system takes advantage of WF to implement document workflows. Windows SharePoint Services hosts the WF engine and enables workflow models to be associated with any SharePoint list, including document libraries. Office SharePoint Server 2007 builds on Windows SharePoint Services to support additional workflow features, including built-in workflows that meet many common needs of information workers and are tightly integrated with the client-side Office system applications.

Access 2007 developers can also use WF by storing data in Windows SharePoint Services lists and associating workflows with these lists. This enables you to implement business rules on the server, where they will run whether users work with the data from their Access applications or from SharePoint pages viewed in a browser. The Office SharePoint Designer is a valuable tool for constructing these workflows. In addition, Access 2007 allows data from multiple SharePoint sites to be linked and combined in a single application, which provides useful querying and reporting capabilities.

Additional Resources

• Access Developer Portal

• Access 2007 Resource Center

• Access Community

• Windows Workflow Foundation

• Microsoft Office Developer Center

About the Author

Andy Baron is a senior consultant with MCW Technologies. He has received the Microsoft Most Valuable Professional (MVP) award every year since 1995. He writes regularly for technical journals and online media, and he creates and presents courseware for Application Developers Training Company. Andy also has been the architect, project manager, or lead developer on many interesting application development projects, and he enjoys helping to turn business requirements into working code.

This article was produced in partnership with A23 Consulting.