Setting up a Table Hierarchy

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.

Microsoft Access Workflow Designer for Microsoft SQL Server™ uses a hierarchy of tables to manage core features and services in the solution database. The hierarchy consists of main, detail, and lookup tables. For more information about the types of tables, see Defining Hierarchy Schema.

The advantage of this hierarchy is 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 also included automatically.

Tables are added to this hierarchy using the Main Table Selection wizard. The hierarchy is displayed under the Tables node in the Object List in the Access Workflow Designer. When a table has been designated as a main table, all related tables are also included in the table hierarchy as either detail or lookup tables. The relationship between the tables is defined through a primary key relationship.

For example, if you have the following related tables in your database:

and you select the Employees in the Main Table Selection wizard, you have the following table hierarchy:

When you use the Main Table Selection wizard to add a table to the hierarchy, the wizard also creates a view of the main table. In addition, the wizard adds permissions to the Public role for that view that are required for data access page interaction. For more information about the views used in a team solution, see Views or search for "view" in the Access online Help.

In addition to the tables you specify, when you add workflow or offline features to your team solution, Access Workflow Designer creates lookup tables used to support those features. These tables are also included in the table hierarchy. For example, when you add workflow to a table, a <*tablename>*StateLookup table is created and displayed in the Object List.

For information about adding tables to your solution hierarchy, see Creating a Table Hierarchy.