Access 2010 Web Databases: How can I create a Compound Unique Index?

Unfortunately, as of Access 2019, Access Web Apps have been discontinued.  From the March 2017 announcement from Microsoft:

“We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.”

As I mentioned in a previous post (Access 2010 Web Databases: Uploading the Data to SharePoint), in an Access client database, I normally create a Compound Unique Index (that is, a unique index on multiple fields) to insure real world uniqueness for a record. Unfortunately, SharePoint does not support compound indexes, so I'll have to simulate it.

Composite Unique Indexes

There are two ways to simulate the effects of a unique index on multiple fields. Both involve the use of Data Macros, which are similar to triggers in other database systems. Data macros are automation that takes place at the table level.

Nulls Allowed
The first method is used when all of the fields in the "index" are required. You Look Up A Record (similar to a DLookUp) and use a with a multiple Where Condition to test whether those values have already been used. In my database project, the linking table (lnkBooksAuthors) has a unique index on the combination of the two Foreign Keys: AuthorID and BookID. Usually, I will use those two fields as a Primary Key. However, as I showed before, SharePoint does not allow multiple field primary keys. So I created an Autonumber primary key, and now I have to simulate a unique index on those fields.

In the BeforeChange event, use the following (click to enlarge):



The full Where Condition (above), looks like this in the Expression Builder:



Allowing Nulls
The second method is used when all of the fields in the "index" are NOT required. For instance, in my tblAuthors table, I want to create a unique index on AuthorFirstName, AuthorMiddleName, and AuthorLastName to make sure I don't get duplicate records. But there's a problem. Authors often don't have middle names, so the AuthorMiddleName field must allow NULL values. But a NULL in any of the values will prevent the expression to valuating to TRUE, so duplicate records could be saved.

To get around this, simulate the unique compound index with a Data Macro on a Calculated Column. In this case, one that looks like "AuthorName" (below):



Ordinarily, I would advise strongly against using them because they violate the Rules of Normalization. But because SharePoint is not a relational database, you will often have to do some things I'd ordinarily frown upon.

Therefore, your data macro would look like this instead:



Using a calculated column has 3 advantages:

1. the Where condition in the macro is shorter and easier to read,
2. it handles the NULL value problem, and
3. this column can be used throughout your application where you need the full name in "LastName, FirstName MiddleName" format.

So this takes care of my Compound Index problem, but I still need to solve the issues with Relationships that I discussed previously. That's what I'll talk about next in Access 2010 Web Databases: Creating Relationships.
.