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
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.
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.
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.
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:
After following these steps, your final team solution includes the following objects:
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
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.
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.
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:
When you are finished, your tblProjects table should look like the one in Figure 2:
Figure 2. Schema for the tblProjects table
Note If you want to set a default for the current date, use the following syntax: (convert(varchar,getdate(),1)).
When you are finished, your tblProjectTasks table should look like the one in Figure 3:
Figure 3. Schema for the tblProjectTasks table
Note If you want to set a default for the current date, use the following syntax: (convert(varchar,getdate(),1)).
When you are finished, your tblProjectTaskAssignments table should look like the one in Figure 4:
Figure 4. Schema for the tblProjectTaskAssignments table
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.
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.
Figure 5. Access Workflow Designer buttons added to Access 2000 database window toolbar
The Web site URL created by the wizard is displayed: http://<Server Name>/ProjectTrackerSQL.
When processing is complete, the Access Workflow Designer appears, and you can begin developing your solution.
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.
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.
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.
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.
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.
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.
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.)
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.
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.
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.
Next, you must create transitions to connect these new states with existing states.
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.
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.
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.
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.
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.
Note From within Access, you could use the Tools menu, select Security, and then click Database Security.
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.
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.
Now, your team solution has three users—dbo, user1, and user2—and two roles. You can grant and revoke workflow permissions to these 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.
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.
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.
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.
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.
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.
For more information on Access Workflow Designer security, see "Security Permissions Model" in the Access Workflow Designer Developer's Guide online Help.
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.
Figure 7. Workflow Process Permissions tab showing permissions set for workflow actions
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.
For examples of workflow script, see "Script Examples" in the Access Workflow Designer Developer's Guide online documentation. Several examples include:
For more details about scripting workflow actions, see "Scripting Workflow Actions" in the Access Workflow Designer Developer's Guide online Help.
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
Function State1_ValidateOnCreate()
State1_validate_OnCreate = True
End Function
Sub State1_OnCreate()
End Sub
Sub State1_OnCreate()
Call SetUser()
End Sub
Sub State3_OnTransition4()
Call SetUser()
End Sub
This completes the workflow for the ProjectTracker.
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.
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.
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.
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.
Note When you have published a database, you cannot make schema changes without uninstalling Offline Replication Components, which drops all Publications.
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".
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.
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.
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.
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.
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.
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.
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.
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.
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.
<p><a href="ProjectTasks.htm">Project Tasks</a>
<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.
Your pages resemble the following example in Figure 10:
Figure 10. Project Information Web page in design view
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.
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.
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.
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.
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 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.
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.
If you have problems with your access data page or making changes to the script, you must re-create your offline page.
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 the latest information about Microsoft Access Workflow Designer for SQL Server, see the Microsoft Office Developer Web site at http://msdn.microsoft.com/OfficeDev/.
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 Office Developer Web Forum at http://search.microsoft.com/us/dev/apps/office/, and search for "data access page."