Team Solution Development Guidelines

  • Article

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.

Whether you are starting development based on a template, creating a data project from scratch, or registering an existing SQL database, there are certain design considerations to understand before beginning development.

Important   When basing a team solution on an existing database, heed the following warnings.

  • Before registering an existing database as a team solution, make a backup copy of the database.

  • Do not attempt design changes on a production database. Move or copy your database to a test environment, and perform all workflow implementation and schema changes there. Once you are sure the team solution is functioning as expected, deploy it to your production server.

The following design guidelines can help you in the overall design process. It is recommended you review them completely before developing your team solution.

  • Database Schema Design Guidelines

  • Workflow Scripting Guidelines

  • Data Access Page (User Interface) Guidelines

  • Offline Publication Guidelines

For additional information about possible issues that can impact development, see Troubleshooting.

Database Schema Design Guidelines

When creating a database you plan to register as a team solution, there are certain schema limitations with which you should be familiar before starting development. These restrictions also should be carefully reviewed if you plan to register an existing SQL Server database. For details, see Creating and Registering Solution Databases and Setting up a Table Hierarchy.

General Guidelines

  • SQL Server makes it possible for the following data types in single column primary keys: datetime, smalldatetime, float, real, decimal, money, smallmoney, int, smallint, tinyint, timestamp, nvarchar, nchar, varchar, char, varbinary, binary, and uniqueidentifier.

  • SQL Server does not make it possible for the following data types in primary/unique keys: text, ntext, image, and bit.

  • Table relationships are only supported in the Access Workflow Designer table hierarchies if they are based on primary key/foreign key relationships where those keys are any keys supported by SQL Server (including multi-column keys). However, row-level security can only be implemented on a main table if it has a single column primary/unique key and that column is of type tinyint, smallint, or int.

  • When you add a main table to a table hierarchy in the Access Workflow Designer, views are created for that main table and any related detail tables. If these tables already have permissions set on them, you must set the permissions on the associated views manually. Access Workflow Designer does not replicate existing table permissions to the newly created views.

Note   Unrelated and lookup tables do not have views created for them.

  • If you create an SQL view in your database after it has been registered, you must apply permissions manually on the newly created view. If this view is to form the basis for a data access page, you must grant SELECT privileges on the primary key and INSERT and DELETE privileges on the other columns to the roles that will be using the data access page.

  • When naming objects, do not use spaces and non-standard keystrokes such as: " /  \ [  ] :  ; |  = ,  + *  ? <  >. Also, avoid using SQL Server keywords, because the name will be interpreted as a keyword and not a database name. For more information, see the "Reserved Keywords (T-SQL)" topic in the SQL Server Books Online.

  • Avoid using database names that have the same starting characters followed by a space and a delineating word, such as, "IssueTracking" and "IssueTracking Test." This may cause problems when subscribing to and synchronizing team solution publications. To avoid this issue, do not use spaces or ensure that the characters before the space are unique.

  • If your team solution has existing offline publications, you must re-create the publications after making schema changes. To re-create the publications, you must:

    • Disable offline replication

    • Make the schema changes

    • Re-create the publications

    • Enable offline replication

Workflow Schema Guidelines

  • You can enable workflow on tables with a single column primary key or unique key. You cannot enable workflow on tables with concatenated keys (more than one column making up the primary key).

  • Workflow status columns must be integer type and must have a foreign key constraint with a keyword lookup table that stores the names of the workflow states. The Workflow Process wizard can create this column for you. For details, see "Schema Requirements for Workflow" in The Workflow Engine Model.

Offline Schema Guidelines

  • You can use SQL system functions in offline publications to filter rows based on contextual information-for example, comparing a column to CURRENT_USER to replicate only those rows assigned to the current user.

  • Timestamp columns are not permitted in tables used in offline publications.

    Note   When upsizing an Access .mdb file to a SQL Server database, there is an option to exclude timestamp columns. Be sure to exclude timestamp columns if you plan to add offline functionality to your team solution.

Row-Level Permissions Schema Guidelines

  • In order to enable row-level permissions, a table should have an integer-compatible column that can be used as row identifier by the code that enforces row permissions. Integer compatible types are int, smallint, tinyint, numeric, and decimal.

    Access Workflow Designer looks first for an int primary key and then for an identity column. The rowID column is provided by the modGetPermissionsJoinColumn stored procedure. For reference information for the stored procedures included with Access Workflow Designer, see Stored Procedures.

Workflow Scripting Guidelines

You can add script to your workflow actions to enhance the workflow process. When scripting, the following guidelines and restrictions should be considered. For details about adding a workflow process to your solution, see Building a Workflow Process.

  • By default, an error raised from a validation or action procedure causes the action's transaction to be rolled back. If you want to prevent this, trap the error using On Error Resume Next, and provide your own error handling.

  • Every action exiting a given state must have a unique name, but it is possible to have multiple actions with the same name entering a state. The Issue Tracking toolbar combines actions with the same name into a single button. The effect of this is you could have a Resolve action that goes from Active to Resolved and another Resolve action that goes from Escalated to Resolved. Although they are two separate actions, they appear to the user as the same thing. Likewise, you can have a modify action called "Edit" on every state that appears as a single button in the toolbar. This user interface effect is something to consider when naming actions.

  • Make sure your script has error handling for cases where user directory information is not available-for example, gracefully handle cases where a user's e-mail address or manager is blank or Null. This could happen when the required information is not available in the master Microsoft Windows NT® domain or Microsoft Exchange directory.

  • You can link multiple workflow processes together by inserting, updating, or deleting rows in other workflow-enabled tables from your workflow script. This can be used to create parallel workflow processes.

  • The validation procedure for a time-out event should return True if the time-out has expired and False otherwise.

  • Do not perform updates from within a validation procedure. This introduces unexpected side effects from the validation procedure, even when the validation returns failure.

  • You cannot show user interface from workflow script, because the script runs on the server.

Data Access Page (User Interface) Guidelines

When developing the user interface for your team solution, consider the following issues. For details about creating the user interface, see Developing the Application User Interface.

  • When creating a data access page for a main or detail table, always bind (set the record source) to the base view (or a view of the base view) and not to the table itself. You must specify a unique base table in the properties of the data access page for the view to be updateable.

  • Access Workflow Designer uses the Microsoft Internet Explorer Web crawling feature to determine the set of pages to replicate for offline use. This should identify any pages, images, and controls referenced from the team solution's default page. If your solution has other files that must be replicated, you can create a Channel Definition Format (.cdf) file using the Open Software Description (OSD) format to provide Internet Explorer with an inventory of the solution files and components.

  • If your team solution uses row-level permissions or multiple offline publications, it is up to the team solution developer to provide a user interface that exposes these features. The Issue Tracking solution provides HTML pages that can be used as samples.

Offline Publication Guidelines

If you plan to create offline publications for your team solution, you should consider the following guidelines. For details about adding offline feature to your solution, see Allowing Users to Work Offline.

  • Merge replication does not support timestamp columns. Timestamp values are generated automatically by the local server and guaranteed unique within a specific database only. Therefore, it is impossible for a change to the timestamp value created on one server to be applied to the timestamp column on another server. You must remove the timestamp column from any table you want to publish using merge replication. For more information, see the merge replication topics in the SQL Server Books Online.

  • Publication names should not contain leading or trailing spaces.

  • You cannot make changes to your tables' schemas that are included in an offline publication while replication is enabled. You can disable replication temporarily using the Access Workflow Designer to make your schema changes. When you re-enable replication, your offline publications will be regenerated. Furthermore, if your schema change affected any columns used in publication filters, you will have to modify the filters for the publications to work.

  • When a user performs an action offline, the validation and action procedures for that action are run on the offline client and then run again when the user's changes are replicated (played back) to the server.

    To ensure you do not run script twice or run server-only script on the client, you can disable specific actions when offline in the Design tab of the Workflow Process pane. For example, if your script uses CDONTS to send mail, it should check the offline setting and only send the mail when online, because CDONTS is not available on the client.

  • If you want to enable your solution for offline use, any Web pages that connect to the solution or system database must be included in the offline redirection script file. The script must call these functions to obtain the correct connection string when online or offline.

  • Because properties for a team solution's offline publications are stored in the modProperties table on the SQL Server, it is important these properties are not changed once the publications have been created. Changing these properties can break replication for the offline subscribers. In the rare case you make changes to the following values stored in the modProperties table, you must re-create any offline publications.

    • OfflineUseIdentityPartition

    • OfflineDefaultConflictResolution

    • OfflineMaxUsers

    • OfflineSeedStart

    Warning   If you change any of these values once subscribers have taken the database offline, the offline subscriptions will be broken, and any users working offline will lose offline changes. All users who have subscriptions to the publication must also re-create their subscriptions. For more information, see Re-creating an Offline Publication.