Developing a Team Solution Using Microsoft Access Workflow Designer

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Elizabeth F. Chapman
Microsoft Corporation

September 1999

Summary: This article contains a step-by-step walkthrough illustrating how to create a team solution using the Microsoft® Access 2000 Workflow Designer for Microsoft SQL™ Server version 7.0. The article presents specific instructions for creating all components of a team solution, including generating the team Web site, creating a Microsoft Access data project, building and relating the necessary tables, producing the workflow objects, and building the user interface. (32 printed pages)

Introduction Building a Team Solution Step 1: Create and Register a SQL Server Database as a Team Solution Step 2: Create a Table Hierarchy Step 3: Create a Workflow Process Step 4: Enhance the Workflow Process Step 5: Add Database Users and Create Roles Step 6: Define Workflow Permissions and Add Workflow Script Step 7: Enable Offline Replication and Create Offline Publications Step 8: Create a Web-based User Interface Using Data Access Pages Step 9: Create a Team Template and Test Your Solution For More Information

Introduction

Use the Microsoft® Access Workflow Designer for Microsoft SQL Server™ 7.0 to create and enforce business rules for your Microsoft Access 2000 data projects.

An Access 2000 data project makes it possible for you to use familiar Access tools to build SQL Server databases. When the Microsoft Office 2000 Developer Access Workflow components are installed, a data project can be registered as a team solution and enhanced with workflow processes to create and enforce business rules.

A user interface comprised of data access pages can be used to create Web views of your database in minutes. The Office 2000 Developer Workflow Toolbar can be added to the page to provide access to the workflow steps, offline capability, and subscription to publications.

In this walkthrough, you create a basic team solution using Access and the Access Workflow Designer. This solution will include a relational database called ProjectTracker, business rules, publications and permissions, and a user interface comprised of data access pages.

Setup Requirements

For this walkthrough, you must have a server and a development/client computer. The server must follow the requirements for server setup detailed in "Access Workflow Designer Server Installation Notes" in the modServerReadme.htm file.

If you are a system administrator on the server, you are not required to install the Access Workflow Designer Tools components on the server. With system administrator permissions, you can set the User Directory synchronization options using your development computer. The development computer, which serves the dual role of the development computer and the location for the offline client testing, must follow the installation and setup requirements detailed in the "Access Workflow Designer Tools Installation Notes" in the modClientReadme.htm file.

To develop solutions, you must be a member of the modAppOwners Microsoft Windows NT® 4.0 or later. This group is created during the installation of the Access Workflow Designer server components. You can be administrator on your development computer, and you minimally must have administrator (sa) permissions on your local copy of SQL Server or Microsoft Data Engine (MSDE).

For this walkthrough, you must create three additional Windows NT user accounts with SQL Server logons. Two users, user1 and user2, can be individuals in your organization who already have Windows NT accounts and SQL Server logons, or you can use test accounts that your system administrator creates for you. The third logon should be for the Windows NT group domain users. Use this group to populate the User Directory.

Building a Team Solution

There are two ways to build a team solution—by registering an existing SQL Server database or using a team template as a boilerplate for a new solution.

Create a Solution from a Database

In this walkthrough, you will create a solution from a database. When you have created your database, you can turn it into a team solution using Access Workflow Designer tools. The overall steps are as follows:

To develop a team solution

Create and register a SQL Server database as a team solution.
Create a table hierarchy consisting of main and detail tables. The table hierarchy makes it possible for you to indicate the tables involved in a workflow process and displays a visual representation of your main and detail table relationships.
Create a linear workflow process using the Workflow Process wizard.
Enhance the workflow process by adding additional states, actions, and branching transitions using the workflow diagram.
Add database users, and create roles. Create role permissions, and assign users to roles.
Define workflow permissions, and add script to provide functionality to workflow actions.
Enable offline replication components, and create an offline publication.
Create a Web-based user interface using data access pages. Use the Workflow Toolbar to add offline and workflow functionality to the pages.
Create a team template for your solution, and test your solution.

After following these steps, your final team solution includes the following objects:

A team Web site.
An Access data project based on a SQL Server database.
The workflow objects, including a table hierarchy, one or more workflow processes, and one or more publications.
Data access pages, designed in Microsoft Access 2000 and stored in the team Web site.

Step 1: Create and Register a SQL Server Database as a Team Solution

Creating the SQL Server Database Using Access

When you create an Access data project (*.adp), the Access Database window includes objects, such as views, database diagrams, and stored procedures, that you do not see when you create an .mdb. These objects are native to SQL Server.

When Access Workflow Designer is installed, three buttons are added on the Access Toolbar, as shown in Figure 1.

Figure 1. Access Workflow Designer buttons added to Access 2000 database window toolbar

To create a SQL Server database using Access

Start Access 2000.
When the Microsoft Access–Create a new database using dialog box appears, select Access database wizards, pages and projects. Click OK.
Select the General tab, and select the Project (New Database) option. Click OK.
Give your project a name, ProjectTracker, and click Create. You can save this project in your personal folder or any alternate location. The location is not critical.

Logging on to SQL Server

The first step to creating a SQL Server database using Access is to connect to an existing SQL Server and provide a name for the new SQL Server database. To do this you must be a member of modAppOwners Windows NT Group on the your server. This group has a SQL Server logon with database creation privileges.

To log on to SQL Server

Open a new Access project (*.adp). A SQL Server wizard opens to help you connect to SQL Server and specify a name for the SQL Server database.
Specify the name of the SQL Server you want to use, or select a server from the list.
Leave the logon name and password blank to use Windows NT authentication.
Provide a database name of ProjectTrackerSQL.
Click Next, and click Finish to complete the wizard with the defaults.
A SQL Server progress meter message is displayed. This process may take a few minutes.

When processing is completed, the Access database window is active. Now, you can design your SQL Server schema using Access.

**Note:**In addition, you can create tables using SQL Server Enterprise Manager. However, be sure to refresh the Access database window before attempting to register your database in the Access Workflow Designer.

Creating SQL Server Database Tables

The possibility of creating a SQL Server database directly through Access is a new feature in Access 2000. If you have an existing Access database (*.mdb) you want to use for a team solution, you must use the Access Upsizing Wizard to convert it to a SQL Server 7.0 database. Then, you can use the Access Workflow Designer to add workflow, row-level security, and offline features to your solution.

When designing your database, it is important to understand the schema requirements and guidelines for a team solution. For more information, see "Planning a Team Solution" and "Team Solution Development Guidelines" in the Access Workflow Designer Developer's Guide online Help.

If you are primarily an Access developer, notice the table design interface is significantly different for SQL Server. For help creating SQL Server database tables, see "Creating and Maintaining Databases" in the SQL Server Books Online.

In this walkthrough, you are going to create three tables:

tblProjects.
tblProjectTasks.
tblProjectTaskAssignments.

To create the tblProjects table

In the Access database window, select Tables from the Objects list. Double-click Create table in Design view.
In the Choose Name dialog box, name this table tblProjects.
Create the first column ProjectID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and select the Identity option. Click the Key button to make this column a primary key.
Create a second column ProjectName as a datatype of nvarchar, with length of 50.
Create a third column ProjectDescription as datatype of nvarchar, with length of 75.
Create a fourth column ProjectBeginDate as a datatype datetime, with length of 8.
Create a fifth column ProjectEndDate as a datatype datetime, with length of 8.
Create a sixth column Completed as a datatype bit, with length of 1, and set a default value of (0), which is False.
Create a seventh column ModifiedBy as a datatype nvarchar, with length of 65.
Click the Save button, and close the table designer.

When you are finished, your tblProjects table should look like the one in Figure 2:

Figure 2. Schema for the tblProjects table

To create the tblProjectTasks table

In the Access database window, select Tables from the Objects list. Double-click Create table in Design view.
In the Choose Name dialog box, name this table tblProjectTasks.
Create the first column ProjectTaskID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and select the Identity option. Click the Key button to make this column a primary key.
Create a second column ProjectID as an int datatype, with length of 4.
Create a third column TaskName as datatype of nvarchar, with length of 50.
Create a fourth column TaskDescription as datatype of nvarchar, with length of 75.
Create a fifth column TaskStartDate as a datatype datetime, with length of 8.

Note: If you want to set a default for the current date, use the following syntax: (convert(varchar,getdate(),1)).
Create a sixth column TaskEndDate as a datatype datetime, with length of 8.
Click the Save button, and close the table designer.

When you are finished, your tblProjectTasks table should look like the one in Figure 3:

Figure 3. Schema for the tblProjectTasks table

To create the tblProjectTaskAssignments table

In the Access database window, select Tables from the Objects list. Double-click Create table in Design view.
In the Choose Name dialog box, name this table tblProjectTaskAssignments.
Create the first column ProjectTaskAssignmentID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and select the Identity option. Click the Key button to make this column a primary key.
Create a second column ProjectTaskID as an int datatype, with length of 4.
Create a third column EmployeeID as datatype of nvarchar, with length of 65. You use this column to link to the team solution user directory.
Create a fourth column DateAssigned as a datatype datetime, with length of 8.

**Note:**If you want to set a default for the current date, use the following syntax: (convert(varchar,getdate(),1)).
Create a fifth column DateCompleted as a datatype datetime, with length of 8.
Click the Save button, and close the table designer.

When you are finished, your tblProjectTaskAssignments table should look like the one in Figure 4:

Figure 4. Schema for the tblProjectTaskAssignments table

Creating Relationships in the Database Diagram Designer

When you create a table hierarchy in the Access Workflow Designer, it examines the table relationships to determine how to create the hierarchy. Therefore, it is important to create your table relationships before registering your team solution or attempting to set up a table hierarchy. To create relationships in the Access data project, use a database diagram.

To create table relationships

In the Access database window, select Database Diagrams in the Objects list.
Double-click Create database diagram in designer option.
On the toolbar, click the Show Table button.
Expand the Tables node. Drag all three tables onto the diagram window.
To create relationships, drag from the primary key of one table to the foreign key of another. Unlike the Relationships feature in an *.mdb, the relationship lines do not necessarily line up primary key to foreign key.
To create a relationship between tblProjects and tblProjectTasks, drag from the primary key, ProjectID, in tblProjects to the foreign key, ProjectID, in tblProjectTasks.
When you release the mouse button, the Create Relationship dialog box appears. Verify that it lists the correct fields, and click OK.
To create a relationship between tblProjectTasks and tblProjectTaskAssignments, drag from the primary key, ProjectTaskID, in tblProjectTasks to the foreign key, ProjectTaskID, in tblProjectTaskAssignments.
When you release the mouse button, the Create Relationship dialog box appears. Verify that it lists the correct fields, and click OK.
Save the diagram, and name the diagram ProjectTracker. Click OK.
Click Yes when prompted to save all tables.
Close the Database Diagram.

Registering Your Database as a Team Solution

When you have a data access project based on a SQL Server database, you can use the Database Registration wizard to begin adding workflow features to your solution. For more details about the requirements for registering a team solution, see "Creating and Registering Solution Databases" in the Access Workflow Designer Developer's Guide online Help.

To register a SQL Server database

With your database open in Access, from the Access toolbar, open the Access Workflow Designer. Figure 5 shows you the Access toolbar.

Figure 5. Access Workflow Designer buttons added to Access 2000 database window toolbar
Because this database is not a registered team solution, the Database Registration wizard appears. Click Next.
On the Registration page, enter your solution name, ProjectTrackerSQL. Do not use spaces.
Enter an optional description, Sample Project Tracking Database.

The Web site URL created by the wizard is displayed: http://<Server Name>/ProjectTrackerSQL.
Click Next. You are notified that the Web site does not exist. Select Yes to have the Web site created for you.
Click Finish.

When processing is complete, the Access Workflow Designer appears, and you can begin developing your solution.

Step 2: Create a Table Hierarchy

Typically, your database includes a combination of main tables, detail tables, and additional miscellaneous tables, such as lookup tables. As you develop your team solution, you might want to specify additional main tables, so you can provide a variety of offline publications or workflow processes within your team solution. For more information, see "Defining Hierarchy Schema" in the Access Workflow Designer Developer's Guide online Help.

Creating a New Table Hierarchy

The advantage of this hierarchy is that detail and lookup tables inherit permissions and properties from the parent main table. In addition, when you specify main tables to be included in offline publications, the associated tables are included automatically.

To set up your table hierarchy

Open your team solution in Access Workflow Designer.
Right-click the Table node, and select Add Main Table. Click Next.
Select tblProjects as your main table. Click Next.
This example does not use row-level permissions, so click Next, and click Finish.
To view the entire table hierarchy, expand tblProjects and tblProjectTasks in the ObjectList.

Notice that you can enable and disable row-level permissions from this page.

Now, the table hierarchy is created, and you can create a workflow process using the main table, tblProjects.

Step 3: Create a Workflow Process

When you create workflow using the Workflow Process wizard, you create workflow states, and the wizard automatically creates transitions between those states based on their order. Behind the scenes, the wizard adds a column to your main table called modStateId. This has a foreign key relationship with a table the wizard creates called <main table>StateLookup. This lookup table contains the Id for each workflow state.

Before launching the Workflow Process wizard, determine the type of states you might want to set. For instance, in a payroll team solution, you might want to create submit, review, and approved workflow states. For this Project Tracker solution, we are creating six states: Analyze, Design, Test, Release, Reanalyze, and Redesign. The first four will be created using the workflow wizard, and the last two will be created using the database diagram.

For an overview of workflow design, see "Designing your Workflow Process" in the Access Workflow Designer Developer's Guide online Help.

Creating a New Workflow Process

The Workflow Process wizard is used to create a new workflow process. When you have created an initial workflow with the wizard, you can modify it using the workflow diagram. For more details, see "Building a Workflow Process" in the Access Workflow Designer Developer's Guide online Help.

To add workflow to your team solution

Open your team solution in Access Workflow Designer.
Right-click the Workflow Processes node, and select Add Workflow Process.
When the Workflow Process wizard launches, click Next.
Select tblProjects if it is not already selected in the tables list.
Select Create a new column. This will create a status column in the main table. Click Next.
Place the cursor in the Add new lookup keyword field, and type Analyze. Click Create.
Place the cursor in the Add new lookup keyword field, and type Design. Click Create.
Place the cursor in the Add new lookup keyword field, and type Test. Click Create.
Place the cursor in the Add new lookup keyword field, and type Release. Click Create,****and click Add.
To specify the order of the states within the workflow process, highlight a state, and click the Move Up and Move Down buttons.

Note: The order of the states is significant because, by default, the wizard generates workflow actions that prevent an item from skipping states. If you want to specify actions that make it possible for skipping states or alternative processing depending on other conditions, you must customize the workflow diagram. For more information, see "Using the Workflow Diagram" later in this walkthrough.
Click Next, verify the information, and then click Finish.

The workflow process is displayed graphically in the workflow pane on the Diagram tab.

Note In the table hierarchy, if you expand the tblProjectTasks, you see that an additional table has been added—tblProjectsStateLookup. In fact, if you switch back to Access and press F5 to refresh the database window, you see many objects have been added to your Access project. (In addition, you can view these objects in the SQL Server Enterprise Manager if you have it installed.)

Step 4: Enhance the Workflow Process

The Workflow Process pane shows the workflow schema graphically and the available actions for the various objects in the diagram. These panes can be resized by dragging the vertical dividing line. The lower pane displays the action and script procedure names and the action's offline availability. Right-click the workflow diagram to view your zoom and layout options. Double-click an action or a transition to display the associated script procedures.

Using the Workflow Diagram

In the workflow diagram, notice, in addition to the workflow states you created, the Start and End blocks. Additionally, notice the connections between the states representing the state transitions—for example, Analyze_Design. You can rename these to something more meaningful to your team solution by selecting a transition and editing the Action name field on lower portion of the workflow diagram.

You can modify your workflow design using this workflow diagram by adding additional transitions between states and additional actions. Right-clicking existing states creates both actions and transitions.

When you add a transition, you create a permissible change from one state to another. In the current workflow design, for example, a record cannot go from Analyze to Closed. That transition would be rejected. However, if you right-clicked Active and added this transition, then the workflow engine would permit this state change.

When you add an action, you are creating a valid activity that can be performed by the database user. For example, click Analyze. You see actions including New and Edit. However, Delete is not available. This means, in your database, you cannot delete a record that is in an Analyze state. If you want to permit this, you must right-click Analyze and add the Delete action.

In addition, adding actions gives you opportunities to trigger scripts. Notice when you click an action in the upper-right corner of the workflow diagram, the associated script procedures are shown in the lower section of the window. Each action has two script procedures—a validation script procedure and an action script procedure. The validation procedure must return True for the action script procedure to be triggered. By default, each validation procedure does return True.

For example, select the Analyze state, and click the New action. In the lower portion of the pane, the State1_Validate_OnCreate() and State1_OnCreate script procedures are shown.

If you double-click the New action in the workflow diagram, you see the associated scripts:

Function State1_Validate_OnCreate()
   State1_Validate_OnCreate= True
End Function
Sub State1_OnCreate()
End Sub

**Note:**You can modify the validation function to interact with your application. For example, perhaps this function only returns a True if a certain user is entering data or if it is a certain day. However, keep in mind if this function does not return a True, the action script procedure will not fire, and the state transition will not be permitted.

For more information on workflow scripting, see "Scripting Workflow Actions" in the Access Workflow Designer Developers Guide online Help.

Adding a Workflow State using the Diagram

The Workflow Process wizard is excellent for creating a linear workflow. However, if you want non-linear workflow states, you must add them manually using the workflow diagram. For this walkthrough, you are adding two states: Reanalyze and Redesign.

To add a workflow state

If necessary, open the ProjectTrackerSQL team solution in the Access Workflow Designer. Open the Workflow Processes node, and select tblProjects. The tblProjects workflow is displayed in the workflow diagram.
Select the Design tab.
To arrange the diagram, right-click the diagram background, and from the shortcut menu, select Arrange Diagram - Vertical. Right-click again, and select Zoom to fit.
Right-click the diagram background, and from the shortcut menu, select Add State.
Type Reanalyze and press OK.
Repeat those steps, and add Redesign.
The designated states are added to your diagram. (If you do not see them, right-click the diagram, and select Zoom to fit.)

Next, you must create transitions to connect these new states with existing states.

Adding a Workflow Transition Action

Now, you have two new states, and you must hook them up to the existing workflow using transitions. (A transition is a special kind of action.) The Add Workflow Action dialog box is used to create both transition actions and regular actions. Transition actions create permissible state changes. Standard actions, such as Delete and Edit, are created using this same method.

For more information on action types, see "Workflow Action Types" in the Access Workflow Designer Developer's Guide online Help.

To add a transition action

You must connect Reanalyze to Design. On the workflow diagram, right-click Design, and select Add Action. In the Add Workflow Action dialog box, specify an action name of Reanalyzed in the Action name box. In the Next state drop-down list, select Reanalyze as the state to which you want to connect.
Click OK.
When something has been Reanalyzed, it must be able to transition back to a Design state, so you must add a transition from Reanalyze back to Design.
On the workflow diagram, right-click Reanalyze, and select Add Action. In the Add Workflow Action dialog box, specify an action name of Redesigned in the Action name box, and select Design in the Next state drop-down list. Click OK.
You can drag the transitions between Design and Reanalyze to adjust the layout of the transitions.
You must do the same thing for Redesign. On the workflow diagram, right-click Test, and select Add Action. In the Add Workflow Action dialog box, specify an action name of Tested in the Action name box. In the Next state drop-down list select, Redesign as the state to which you want to connect.
Click OK.
When something has been Redesigned, it must be able to transition back to the Test state, so you must add a transition from Redesign back to Test.
On the workflow diagram, right-click Redesign, and select Add Action. In the Add Workflow Action dialog box, specify an action name of Redesigned in the Actionname box, and select Test in the Next state list. Click OK.
You can drag the transitions between Test and Redesigned to adjust the layout of the transitions.

Note: This made it possible for you to create two transitions called Redesigned—one between Redesign and Test and another between Reanalyze and Design. This is possible because the transitions are tracked using an ID rather than a name (look at the tblProjectsWorkflowActions in your database for more details). However, although this is made possible, it may be confusing to users and difficult to document.
To change the transition name, select the Redesigned transition between Redesign and Test, and in the Action name text box in the lower pane, change it to Retested. Select Save to save your workflow diagram changes.

Your workflow should look similar to that in Figure 6:

Figure 6. Workflow Diagram showing workflow actions

Next, you must set up user information, so you have an accessible list of database users to utilize in the solution as an employee lookup.

Step 5: Add Database Users and Create Roles

To assign users and groups to database roles, the users and groups must have valid Windows NT domain accounts and SQL Server logons. For details, see "Creating Windows NT User and Group Accounts, and Creating SQL Server Logins" in the Access Workflow Designer Developer's Guide online documentation.

This walkthrough is simplified by having two users, user1 and user2, with valid Windows NT accounts and SQL logons. They can be test accounts that your system administrator creates for this walkthrough or any individuals with domain accounts.

Note Generally, you probably have Windows NT group accounts for various categories of users, and you would assign these groups to a SQL Server logon. Then, this group is added as a database user and assigned to one or more roles. For example, to grant everyone access, you can use the Windows NT group account, Domain Users.

Creating Database Roles

To manage the permissions in your solution database, it is recommended you define a set of roles based on job functions and assign each role the permissions that apply to that job.

SQL Server roles exist within a database and cannot span more than one database. Because roles are unique to each database, you can reuse a role name, such as Reviewer in each database you create.

To add database users

With the Access data project loaded, in the Access Workflow Designer, from the Tools menu, click Database Security.

Note From within Access, you could use the Tools menu, select Security, and then click Database Security.
On the Server Logins tab, make sure both users are listed. If they are not listed, select Add and create a SQL logon as long as they have valid Windows NT accounts and you have appropriate permissions on the server.
Select the Database Users tab, and click Add.
From the Login name drop-down list,****select a user, user1. Notice by default they are permitted in the public database role.
Click OK. User1 is listed under database users. Repeat the steps to add user2.

For this walkthrough, there are three database users—dbo, user1, and user2. Now, you create roles and assign these users to specific roles in this database.

To create database roles and assign users

Create two roles. One is a Developer role, for individuals who review the information but cannot do certain workflow actions or make certain transitions. The other is a Manager role, for individuals who create and delete projects and make certain state changes, such as setting a project to the Reanalyze or Redesign state.

With the Access data project loaded, in the Access Workflow Designer, from the Tools menu, click Database Security.
On the Database Roles tab, select Add.
In the Name box, type Manager.
Under User, click the Add button to see a list of database users.
Select user1, and click OK to assign it to the Manager role.
Click OK.
Select Add again, and create a Developer role.
Under User, click the Add button to see a list of database users (including the Manager role you just created).
Select user2, and click OK to assign it to the Developer role.
Click OK again to return to the security dialog box.
Click OK to close the dialog box.

Now, your team solution has three users—dbo, user1, and user2—and two roles. You can grant and revoke workflow permissions to these roles.

To assign permissions to roles

Now the roles are created, and you must specify the permissions for these roles. Role permissions are separate from permissions you designate for the workflow. Role permissions specify the privileges that members of these roles have on the server. For example, if a role does not have select permissions, members of the role are not able to see any data; if the role does not have insert permissions, members cannot add a record. For more information about role permissions, see "Defining Permissions for Database Roles" in the Access Workflow Designer Developer's Guide online Help.

With the Access data project loaded, in the Access Workflow Designer, from the Tools menu, click Database Security.
On the Database Roles tab, select Public, and select Edit.
Click Permissions.
You must grant select permissions on four objects, tblProjects, tblProjectsView, tblProjectTaskAssignmentsView, and tblProjectTasks. Although it appears as though select permissions have been already granted, these permissions are granted on the primary key only. This is done using T-SQL as part of the workflow creation. However, for the user interface to function properly, select permissions must be granted on the entire table.
To reset the select permissions, scroll to each object, and select the Select check box one time to get a red X, a second time to clear the check box, and a third time to return a check mark to the box. (It is important to select it all three times.) Click Apply.
Click OK.
Edit Public permissions again, and add insert, update, and delete****permissions to three tables: tblProjects, tblProjectTasks, and tblProjectTaskAssignments.
Click Apply. Click OK.
Click OK again to return to the security dialog box.
Click OK to close the dialog box.

Notice that by granting the required permissions to Public, all database users inherit them. In the next step, you modify workflow permissions, so only certain roles can perform certain actions. This is one way to manage permissions—being lenient on the role permissions and then tightening security using the workflow permissions.

Synchronizing the User Directory with Your Exchange Server

The user directory lists all users of team solutions on a particular server. This directory information is displayed in a view (called modUserList) for each team solution, which limits the list to role members for that particular team solution.

All database users of your solution are displayed by default, because they are added to the Public role automatically. In the following steps, you add the Windows NT group account domain users to the Public role, so all domain users are available in the user directory. In the next section of the walkthrough, permissions will be revoked from the public role, so database users will have no database privileges unless they are assigned to a role other than Public.

Before you are able to synchronize with Microsoft Exchange, an individual with administrative privileges on the server must setup the synchronization options. If you have Windows NT administrative privileges, then you can do this from your developer machine. If you are not a Windows NT administrator, then the Windows NT administrator must install the development components on the server and launch the Team Solutions Manager on the server.

For more details, see "The User Directory" in the Access Workflow Designer Developer's Guide online Help.

To setup user directory synchronization

Open the Team Solutions Manager.
Select your SQL Server from the Server list, and click Refresh.
On the User Information tab, click Synchronization.
Select the Get directory information from Microsoft Exchange Server option.
In the Exchange Server field, enter your Exchange Server name.
Click OK.
If a message appears informing you data could be overwritten, click Yes.
Click SynchronizeNow. Your user directory is populated based on information from Windows NT and Microsoft Exchange. Minimally, you should see three users—you, user1, and user2.
When the synchronization is complete, leave the window open, and switch to Access.

If you want to use the user directory as a source for user information in your team solution, you can use the modUserList view in your solution as a lookup. At this point, it would be of limited value, because it only contains three database users. To get a list of all domain users, you may add the Windows NT Domain Users group to the public role.

To add additional users to the user directory

In Access, from the Tools menu, select Security, and then select Database Security.
Make sure Domain Users have a SQL Server Login. On the Server Logins tab, see if you have <Server Name>\Domain Users listed. If not, click Add. In the Authentication section, select your domain server from the list. In the Name box, type Domain Users, and click OK.
On the Database Users tab, click Add.
From the Login name list, select <Server Name>\Domain Users, and click OK.
Switch back to the Team Solutions Manager, and click Synchronize Now.

When synchronization is complete, all domain users are displayed on the User Information tab. In addition, although you did not add them to a role, the domain users are displayed in the modUserList view, because when they are added as database users, they are automatically assigned to the Public role.

Now, user information is available for the team solution, and the next step is to assign permissions to the solution roles and enhance workflow functionality using script.

Step 6: Define Workflow Permissions and Add Workflow Script

Permissions added to workflow transitions ensure that only members of designated roles can perform the actions. By default, when a new action is added, all users are permitted to perform the action. In the ProjectTracker, only the Manager role members should be permitted to create and delete projects and set the states to Reanalyze and Redesign. Therefore, permissions must be revoked for public (because all database users inherit Public permissions), and permissions must be adjusted for the Developer role.

Defining Workflow Permissions

For more information on Access Workflow Designer security, see "Security Permissions Model" in the Access Workflow Designer Developer's Guide online Help.

To restrict user permissions for specific workflow actions

Open the ProjectTracker Team solution in the Access Workflow Designer, and expand the Workflow Processes node.
Select tblProjectsWorkflow, and in the right-hand pane, select the Permissions tab. A grid view with rows containing states and state actions and columns containing each role is displayed.

Note If the roles you have created are not displayed, press F5 to refresh the solution, and save if prompted.

You must remove permissions from the public role. Everyone inherits public permissions; so if you are limiting permissions based on an individual's role, you must first revoke public permissions.
Right-click the word public, and select Revoke Execute.
Under the Developer role, remove****permissions for Analyze/New, Release/Delete, Design/Reanalyzed, and Test/Tested. The grid should look like that in Figure 7 when you are done:

Figure 7. Workflow Process Permissions tab showing permissions set for workflow actions
Select Save to update your team solution.

Adding Script to Workflow Transitions

Script can be added to workflow state actions to make several things happen. Actions associated with each state are used to trigger script much like event procedures in Access. To view the available actions, click a state in the workflow diagram. On the right side of the diagram, the Actions list keeps track of the actions used to call procedures.

In this walkthrough, you add a sub procedure called SetUser to the common script area. This is similar to creating a global module in Access. When called by an action, this procedure sets the ModifiedBy field in tblProjects to the current user. You will use two actions to call the SetUser procedure—New and Release. This will update the ModifiedBy field when a new project is created and when it is set to the release state.

Workflow Script

For examples of workflow script, see "Script Examples" in the Access Workflow Designer Developer's Guide online documentation. Several examples include:

Finding a User's E-mail Address in the User Directory.
Sending Mail using Simple Mail Transfer Protocol (SMTP).
Finding a Manager in the User Directory.
Sending Mail to a Manager.

For more details about scripting workflow actions, see "Scripting Workflow Actions" in the Access Workflow Designer Developer's Guide online Help.

To add script to workflow transitions

In the ObjectList of the Access Workflow Designer, open the Workflow Processes node, and select tblProjectsWorkflow.
Select the Shared Script tab. Function State1_Validate_OnCreate() is the first item at the top of the Shared Script area.
The following procedure enters the current user's SAMAccountName in the ModifiedBy field. You can use this syntax when you want to insert the current user into a field. Change the Session.item(<Field Name>) to the field you want to update with the current user's name. Type the following code into the common script dialog box. Or, if you are reading this document online, you can copy and paste it.
```
sub SetUser()
'Session object represents the current recordset
'this snippet sets the ModifiedBy field equal to the current user 
   Session.item("ModifiedBy") = Session.User
   Session.item.updateBatch(3)
end sub
```
To set this up so the ModifiedBy field is updated when a new record is created, this procedure must be called from OnCreate for the New action. Select the Design tab to switch back to the workflow diagram. Double-click the New transition. The scripting area looks something like this:
```
Function State1_ValidateOnCreate()
   State1_validate_OnCreate = True
End Function
Sub State1_OnCreate()
End Sub
```
Between the Sub State1_OnCreate() and the End sub, type Call SetUser(). It looks like this:
```
Sub State1_OnCreate()
   Call SetUser()
End Sub
```
If you want to document which user releases a project, you can call this same procedure from the Test_Release transition. Select the Design tab, and double-click the Test_Release transition.
Under Sub State3_OnTransition4(), type Call SetUser(). The result is this:
```
Sub State3_OnTransition4()
   Call SetUser() 
End Sub
```
Click Verify Script.
To ensure records are not deleted while the team solution is being used offline, select the Design tab. Select the Delete transition, and in the lower pane, clear the This action can be performed offline option.
Click Save.

This completes the workflow for the ProjectTracker.

Step 7: Enable Offline Replication and Create Offline Publications

Now, the major features of the project are created, and you are ready to add offline support to the team solution. An offline publication is a definition of all the objects required to create the local (replicated) copy of the database. The publication contains information about the database and the Web files for the team solution.

Through offline publications, you can provide users with a local copy of the database and make it possible for them insert, delete, or update data without a connection to the online database server. After reconnecting to the network, users can synchronize the local copy of the solution with the online copy.

Offline Replication and Publication

For example, if a project manager visits a job site, a laptop that has an offline copy of the team solution can be used to enter projects or assign tasks. When the project manager returns to the office and connects to the network, the online database can be updated with the changes made to the local version.

Your solution can have multiple publications, each with different filters and permissions, for use by database users in different roles.

Before creating an offline publication, you must install and enable offline replication for your team solution.

For more information about creating offline publications, see "Allowing Users to Work Offline" in the Access Workflow Designer Developer's Guide online Help*.*

To install offline replication capabilities

Open the Access Workflow Designer.
In the Object List, select ProjectTrackerSQL.
In the Solution Properties pane, select the Offline tab.
Click Install Offline Replication Components. This may take a few moments.
When installation is completed, you are prompted to create an offline publication. Click Yes to create a publication.

To create an offline publication

When the Welcome screen is displayed, click Next.
Under Publication name, enter ProjectTackerPub, and for optional description enter example. Click Next.
Under Main User Tables, select the tblProjects table. (When you select a main table, all associated detail and lookup tables are included in the publication.) Click Next.
Under Offline Permissions, specify the database roles that have access to the publication. Click Select All, and clear the public option, so only users in the roles you created can take the team solution offline. Click Next.
Click Finish.

Note A rowguid column is added to all tables included in the publication. This is used for replication. When you have created an offline publication, you cannot make schema changes to your database without first removing offline replication, which in turn drops your publications.

The publication may take several minutes to register on your server.

Specifying a Synchronization Method and a Conflict Resolution Option

To specify synchronization options

In the Object List, select ProjectTrackerSQL.
In the Solution Properties pane, select the Offline tab.
Set the synchronization type to One record at a time. This submits a transaction for each updated record from the client to the server.
Set the conflict resolution option to Skip conflicting rows. This ignores the transactions submitted by the offline publication if they conflict with information on the server.
If you use identity columns in your database schema and want to make it possible for multiple users to take the team solution offline, select Enable identity partitioning. This ensures the identity columns (such as an Autonumber column in Access) remain unique in the offline publications.
Click Save.

Adding an Offline Replication Filter

Offline publication filtering provides a mechanism for limiting the information contained in a publication. For example, if our project manager is taking the application offline to make it possible to enter and update task assignments while traveling from job site to job site, there is no reason to have completed projects. You can create a filter, so only unfinished projects are replicated to the local database.

For a list of all of the available filtering criteria, see "Offline Publication Filtering Reference" in the Access Workflow Designer Developer's Guide online Help.

To add or edit filters for a publication

In the Access Workflow Designer, expand the Offline Publications node, and select ProjectTrackerPub.
In the Offline Publication pane, select the Filter Items tab.
Select tblProjects, and click Edit Filter.
In the Offline Replication Filter dialog box, select Completed in the Column list.
In the Operator column, select Not Equal as the comparison operator.
In the Value column, select True.
Click Add. This ensures only projects where the Completed field is False are replicated to the offline database.
Click OK.
Click Re-create SQL Publication. When you are prompted to save changes, select Yes. When you are prompted to re-create the publication, select Yes again.

Note When you have published a database, you cannot make schema changes without uninstalling Offline Replication Components, which drops all Publications.

Step 8: Create a Web-based User Interface Using Data Access Pages

You can use any form of user interface that has read/write capabilities on a SQL Server database. However, only data access pages support the full set of Workflow Designer features, such as offline replication.

A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet—data that is stored in an Access database or SQL Server database.

For more information about working with data access pages, refer to "Creating the User Interface" in the Access Workflow Designer Developer's Guide online Help or "Data access pages: What they are and how they work" in the Access online Help. For information regarding alternate user interface options, refer to the article "Microsoft Access Workflow Designer: Creating a Team Solution User Interface Using Microsoft Visual Basic".

Creating Team Solution Data Access Pages

When you create data access pages for a team solution, you base the page on a view—not a table, because role security is enforced through the views. In the following examples, you create pages using the tblProjectsView, the tblProjectTasksView, and the tblProjectTaskAssignmentsView.

To create a new data access page using a wizard in Access

Open your team solution in Access. In the Access Objects list, select Views, and select tblProjectsView.
On the toolbar, from the New Object list, select Page, or from the Insert menu, select Page.
Select Autopage: Columnar, and click OK.

To customize the page

Click View, and select Design View.
Right-click the grid area (what would have been called the Detail area in a form or report), and select Properties.
Select the Data tab, and set the UniqueTable property to tblProjects. (This is in preparation for the Workflow Toolbar.)
In the Click here and type title text area, type Project Information.
Click below the title, and from the Insert menu, select ActiveX Control, and double-click Microsoft Office Developer Workflow Toolbar.
Add the toolbar control to the page. This automatically binds it to the workflow rules by inspecting the UniqueTable property on the data source control. Stretch it out horizontally to display all the states and transitions.
The workflow toolbar depends on the modStateID field, so you cannot delete this field. However, you can hide it. Leave it displayed for now.
The rowguid column is used for replication. It is not required that you display it on the data access page. You may set the Visible property to hidden. (You must do this for both the text box and label.)
Click Save, and enter the full path to the Web site URL created when you registered the team solution: http://<Server Name>/ProjectTrackerSQL/ProjectInformation. The next time you save a data page, you can select Web Folders, and the ProjectTrackerSQL team Web on the specified server will be displayed.

Note: You can view the location of your team Web site URL by minimizing your data page, opening the Access Workflow Designer, and selecting ProjectTrackerSQL from the Object List. On the General tab, the team solution information is displayed, including Web URL.

**Tip **If you accidentally save a data access page to a site other than your team Web site, you will never get the option to Save As and change the location. A trick is to select the page in the database window and cut it. Then, when you paste it back into the database window, you can specify the location.
From the File menu, select Web Page Preview to see the toolbar.

The toolbar should appear like the one in Figure 8:

Figure 8. Workflow toolbar as it appears on data access page

Notice that impermissible transitions and actions are disabled. When you are in a blank record, the only permissible action is New. Furthermore, New is only available if you are a member of the Manager role.

To enter information in your data page

On the toolbar, select New to start entering a new project. Notice the value of modStateId is set to 1.
Enter a ProjectName of Project 1, a ProjectDescription of First Project, and a beginning and ending date. Set Completed to False.
Click Save Record. See Save Record on the toolbar in Figure 9.

Figure 9. Record navigation toolbar as it appears on data access page

When the record is saved, the ModifiedBy field is updated to the current user (courtesy of the SetUser() procedure placed in the workflow script)—in this case, your SAMAccountName. In addition, the rowguid is entered automatically.

Now, you can adjust the design of the data page. The design controls are similar to those in the Access form and report design environments. However, the properties for these pages are quite different. In the Access Help, use the Answer Wizard, and search for "data access page design." You get many helpful topics about designing, creating, and using data access pages.

To create the Project Task Information data access pages

In the database window, select the Views object.
Select the tblProjectTasksView.
Click the New Object button, and select Page.
Select Autopage: Columnar. Click OK.
From the View menu, select Design View.
In the Click here and type title text area, type Project Tasks.
To replace the ProjectID text box with a lookup, delete the ProjectID field; from the toolbox, click the DropdownList control, and add it to the form.
When the wizard opens, select the I want the combo box to look up the values in the table or view option. Click Next.
Select the tblProjects table. Click Next.
Add ProjectID and ProjectName to the Selected Fields list. Click Next.
Adjust the width of the ProjectID column, so it is hidden. Adjust the ProjectName column as required to display the expected values. Click Next.
Under Available Fields, ensure ProjectID is selected as the bound field. Click Next. Click Finish.
Right-click the DropdownList control, and select Properties.
Click the Data tab, and set the ControlSource to ProjectID.
Ensure ListBoundField is set to ProjectID.
Ensure ListDisplayField is set to ProjectName.

Note: To reset the tab order after adding the drop-down list control, use the TabIndex property on the Other tab in the Properties window.
Close the Properties window.
Click Save.
Select Web Folders. Double-click ProjectTrackerSQL on <Server Name>. Enter a page name of ProjectTasks, and click Save.

To create the Project Task Assignments data access pages

In the database window, select the Views object.
Select the tblProjectTaskAssignmentsView.
Click the New Object button, and select Page.
Select Autopage: Columnar. Click OK.
From the View menu, select Design View.
In the Click here and type title text area, type Project Task Assignments.
To replace the ProjectTaskID text box with a lookup, delete it. Then, from the toolbox, add the DropdownList control to the form.
When the wizard opens, select the I want the combo box to look up the values in the table or view option. Click Next.
Select tblProjectTasks table. Click Next.
Add ProjectTaskID and TaskName to the Selected Fields list. Click Next.
Adjust the width of the ProjectTaskID column, so it is hidden. Adjust the TaskName column as required to display the estimated values. Click Next.
Under Available Fields, ensure ProjectTaskID is selected as the bound field. Click Next.
Change the label to Task, and click Finish.
Right-click DropdownList control, and select Properties.
Click the Data tab, set the ControlSource to ProjectTaskID.
Ensure ListBoundField is set to ProjectTaskID.
Ensure ListDisplayField is set to TaskName.

Note: To reset the tab order after adding the drop-down list control, use the TabIndex property on the Other tab in the Properties window.
Close the Properties window.
Click Save.
Select Web Folders. Double-click ProjectTrackerSQL on <Server Name>. Enter a page name of ProjectTaskAssignments, and click Save.

The final step is to create a lookup to the user directory information you synchronized earlier. To do this, you must create a drop-down list control that displays information from the modUserList view. For more details about linking to user information, see "Linking Tables to the User Directory" in the Access Workflow Designer Developer's Guide online Help.

To create a lookup to the User Directory

Delete the EmployeeID field from the ProjectTaskAssignments data page.
From the toolbox, click the DropdownList control, and add it to the form.
When the wizard opens, select the I want the combo box to look up the values in the table or view option. Click Next.
Select the Views option, and select modUserList view. Click Next.
Add SAMAccountName and CN (full name) to the Selected Fields list. Click Next.
Adjust the width of the SAMAccountName column, so it is hidden. Adjust the CN column as required to display the values, and click Next.
Under Available Fields, ensure SAMAccountName is selected as the bound field. Click Next.
Change the label to Employee, and click Finish.
Right-click the DropdownList control, and select Properties.
Click the Data tab, and set the ControlSource to EmployeeID.
Ensure ListBoundField is set to SAMAccountName.
Ensure ListDisplayField is set to CN.

Note: reset the tab order after adding the drop-down list control, use the TabIndex property on the Other tab in the Properties window.
Close Properties window.
Click Save.

The final enhancement to the data access page in this walkthrough is the addition of navigation links on the ProjectsInformation page.

These will be hyperlinks added using the Microsoft Script Editor.

To add navigational links the ProjectInformation page

Open the ProjectInformation page in design view.
Right-click the page, and select Microsoft Script Editor.
Scroll through the page until you see the Workflow Toolbar, tblProjects. If you do not see it, on the View menu, ensure the View Controls Graphically is selected.
There is a </P> directly under the toolbar. Add the following HTML code after the </P> tag:
```
<p><a href="ProjectTasks.htm">Project Tasks</a>&nbsp;&nbsp; 
   <a href="ProjectTaskAssignments.htm">Project
Task Assignments</a></p>
```
Note: If you paste the text from a Microsoft Word document, use the Paste as HTML option from the Edit menu in the Microsoft Script Editor.

This code creates links to the Project Tasks and Project Task Assignments pages. Notice that, because all the solution pages reside in the team Web, the <a href> tag does not include a server and path.
Click Save, and close the script editor.

Your pages resemble the following example in Figure 10:

Figure 10. Project Information Web page in design view

Step 9: Create a Team Template and Test Your Solution

A team template is a blueprint for a team solution. The template contains all of the information required to create a complete team solution, including the database schema, workflow rules, lookup tables, forms, and views.

Note: Because the template only saves objects stored on the server, items in your local Access data project, such as forms, reports, and database diagrams, are not stored in the template.

Templates provide a quick way to create several customized team solutions based on your original schema.

In this walkthrough, the ProjectTracker, you might want to create a template and then create new team solutions based on the template at various branch offices. This makes it possible for you to create the basic schema once and then make minor (or major) modifications required for each branch office.

For more information about templates, see "What Happens When You Create a Template and The Team Template" in the Access Workflow Designer Developer's Guide online Help*.*

To save a solution as a template

Open the team solution in Access Workflow Designer.
From the File menu, select Save as Template.
In the Team Template wizard, click Next.
Enter ProjectTrackingExample as the name and Project Tracking Example Template as the title for the new template. Enter version information and a description if desired. (This information is available on the Templates tab in the Team Solutions Manager.) Click Next.
By default, the data is included with the solution. To remove data, clear the check boxes next to each table. Click Next.
To include database users in the template, select Include current users in the new team template. Click Next.
Click Finish. The Team Template wizard backs up the solution database, truncates any specified tables, removes database users if desired, and stores the backed-up database information in a template in the template directory. In addition, it enumerates all of the files in your team Web site and stores them in the template directory.
Close the Access Workflow Designer.

When this template is created, you can quickly and easily create new team solutions based on it. All the work done in this example is rolled up in the template.

Taking a Team Solution Offline

Offline support makes it possible for a team solution to be stored and used on any computer not connected to the network and then synchronized with the team solution on the server. Microsoft Internet Explorer provides the user interface for offline replication, making it easy for users to take the solution offline.

To test the offline functionality of your team solution, it is best to set up a separate client machine, log on as a non-administrative database user, and disconnect from the network when the team solution has been taken offline.

However, for this walkthrough, the development machine is being used as the offline workstation.

Setting up a Trusted Site

By setting up a trusted site, users will not receive error messages about sending and receiving data from this source. It is a good idea for you, as a developer, to set this up for them. It is one less variable in the user experience.

To set up a trusted site

Start Internet Explorer 5.
From the Tools menu, select Internet Options.
On the Security tab, click the Trusted sites icon.
Click Sites.
Clear the Require server verification (https:) for all sites in this zone option.
Under Add this Web site to the zone, type http://<Server Name>.
Click Add.
Click OK.
Click OK again to close the Internet Options dialog box.

Making a Web Page Available Offline

Making a Web page available offline is straightforward. However, if you make a page available offline and then change it, you must follow the instructions provided later in "Offline Web Page Troubleshooting."

To make a Web page available offline

Start Internet Explorer 5.
Enter the URL of the data access page you created, http://<Server Name>/ProjectTrackerSQL/ProjectInformation.htm.
From the Favorites menu, select Add Favorites.
Select the Make available offline option.
Click OK. A Synchronizing dialog box appears.
From the Internet Explorer Tools menu, select Synchronize.
In the Items to Synchronize dialog box, click Synchronize.
From the Internet Explorer File menu, select Work Offline.
Click Refresh.
When Web page unavailable while offline is displayed, select stay offline.

Testing the Offline Read/Write Experience

To work offline successfully, your SQL Server service must be running. Figure 11 shows the service icon indicating that the SQL Server is running. For this walkthrough, you are testing the sample as yourself, the dbo owner. To make the example more realistic, set up a separate client machine, or try logging in as user1, Manager's role, and user2, Developer role, and see how the available actions and transitions change based on the user's workflow permissions. Figures 12 and 13 show the current options available on the Workflow Toolbar and Record Navigation toolbar.

Figure 11. MS SQL Server Service running icon on the Windows Task Bar

Figure 12. Workflow Toolbar options

Figure 13. Record navigation toolbar that appears on data access page

Before testing your workflow offline functionality, follow the instructions for "Making a Web Page Available Offline" earlier in this article.

To test offline features

Take your solution offline.
Unplug your computer from the network.
Try entering and editing records.
Click the Save Record button.
Verify the ModifiedBy field is updated to <Server Name>\SAMAccountName.
Change the Status of the record to Test_Release. Remember you must click Save Record after each state transition.

Note: Delete does not become active, even when the record has been transitioned to Test_Release. The Delete action is set to Not available offline, therefore it is disabled.
Plug your computer back into the network.
From the Internet Explorer 5 File menu, select Work Offline. This should take you back online. Select Refresh.
From the Tools menu, select Synchronize to synchronize the database changes.
Notice that Delete is available for records with a modStateId of 4.
Close Internet Explorer, and open the Web page to verify the changes.
Verify the changes have been replicated to the server.

Offline Web Page Troubleshooting

If you have problems with your access data page or making changes to the script, you must re-create your offline page.

Start Internet Explorer 5.
Enter the URL of the data access page you created, http://<Server Name>/ProjectTrackerSQL/ProjectInformation.htm.
From the File menu, verify the Work Offline option is not selected.
From the Internet Explorer Favorites menu, select Organize Favorites, and then select the favorite for ProjectTracker.htm.
Clear the Make available offline option, and delete the shortcut.
Select Close.
From the Tools menu, select Internet Options.
On the General tab, under Temporary Internet Files, select Delete Files.
Select the Delete all offline content option.
Click OK, and then click OK again to close the Internet Properties dialog box.

Now, you can re-create a favorite for the Web page. For more information, see "Making a Web Page Available Offline" earlier in this article.

For More Information

For the latest information about Microsoft Access Workflow Designer for SQL Server, see the MSDN Office Developer Center at http://msdn.microsoft.com/office/.

To access Knowledge Base information, consult the Product Support section of the Microsoft Office Developer Web site.

For information about developing and managing team solutions, consult the Access Workflow Designer Developer's Guide and the Access Workflow Designer Administrator's Guide in the online documentation.

For additional information about Microsoft Access data access pages, refer to http://msdn.microsoft.com/, and search for "data project" and "access data page."

Or refer to Microsoft Developer Network at http://search.microsoft.com/default.asp?siteid=us/dev, and search for "data access page."