In this article we take a more in-depth look at how the support for referential integrity in an existing Access database is to converted to a Web Database, and we see how the new options in the lookup wizard have been added to support the construction of web legal relationships which will enable you to more easily convert your existing Access databases.

Avoid Cascade Updates

Because Access will NOT allow a cascade update on an Autonumber which is acting as a primary key and most of your primary keys in a web legal database will be Autonumbers this is not really a big issue. But if you have a primary key which is a simple Numeric Long, you will find that if the relationship has a Cascade Update it will convert to a web database successfully, but that this feature will not be supported if publishing the table to SharePoint.

If you convert database containing a lookup which originally has a cascade update defined for it, then if you attempt to modify the lookup in the web database you will get the following message.

So before performing a conversion any cascade updates on relationships should be REMOVED. It is worthwhile noting this point because it helps explain the options available with the new enhanced lookup wizard.

Lookup Wizard Enhancements

The lookup wizard has always helped in creating a relationship between tables, after the lookup wizard creates a simple relationship it could then be modified to enforce referential integrity. This feature has now been enhanced in Access 2010, and it is worth noting that it creates referential integrity settings which are going to be Web Legal.

When using the lookup wizard in a standard Access database, you will notice a new final page is displayed as shown below.

There are three basic options here, and when you make a choice to enable referential integrity the Wizard not only creates a relationship but makes the correct settings in the referential integrity as shown below.

The possible options for the lookup wizard are as follows:-

• No data Integrity: this generates a simple relationship (like previous versions of Access).
• Restrict Delete: switches on referential integrity with no Cascades.
• Cascade Delete: switches on referential integrity with a Cascade Delete.

You can see that there is no option for a Cascade Update; this is because the Cascade Update is NOT web legal (as described in the previous section).

Notice that the lookup wizard by creating relationships ensures that lookups and relationships are balanced, for each relationship a lookup and each lookup a relationship.

After conversion you can carry out some tests on deleting data you will find that the new Web Database correctly enforces the old referential integrity rules using the new web lookups.

Top of Page

Top of Page