Tuesday, May 18, 2010

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

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)

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.


Admin said...

This worked out great to be able to post on the web database. But this cause me one problem. It didnt allow me to edit any of the fields in that row, because before any change it would find it being a duplicate. If there was a way of doing this only on insert, and only testing on change of the uniqueness. Is this possible?

Roger's Access Blog said...

Unfortunately, I don't have an answer for that. You might ask this question on www.utteraccess.com. They have a forum for web databases and one of the regulars might have an answer: http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html

Admin said...

Thank you for your response! I quickly found a soloution using the search option.


All that is needed, is a If statement with the following
If [IsInsert]=true Or Updated("UNIQUE") then

Keep up the great work!

rachel moore said...

Thank you, thank you, thank you!!!! I have been searching for this answer for HOURS!!! It's wonderful that MS has provided SharePoint and Access integration to provide quick (at least supposed to be quick) solutions for smaller databases; however it is very frustrating that they take away data integrity features in web databases but do not make it clear how to work around those stringent restrictions. Again, thank you for this article. It will help me so very much!! :)

Jose J. Hernandez said...

Thaaaaaaaank you! for sharing your talent. Practical, useful! Thanks.. again

Irfan Ghumman said...

Hi Admin, i have similar problem. I will really appreciate if you write full macro code.

Remy said...

Hi Admin, thanks for the great post. Strangely, this BeforeChange macro is not allowing me to make any changes to an existing record.

The error triggers when I try edit the table directly in Access. Have you come across this before?

I am now looking for a way to exclude the current record from the LookUpARecordIn action.

If you have a solution to this, I would appreciate it since I am very new to Access.

Remy said...

Hi Admin, I just left you a question about this issue, but have since solved it through the comments here: http://blogs.office.com/b/microsoft-access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx

Which might be worth mentioning in your article.

That is: If you have more fields than the ones you want to index, you will need an extra expression to make this macro allow changes to those other fields.

Thanks again!

Unknown said...

Hi Roger - I wonder if you have any similar experience with this problem in Access 2013 Web databases? I am trying to get it to work but I notice a couple of things: firstly at the table level the macro event names are "On Insert" and "On Update", rather than "before insert" and "before update". The problem I then have (e.g. on inserting a row)which may be caused by this is that the error is triggered regardless of what values I put into the 2 "key columns".

In my case the table I am using is "stakeholders" and the 2 "key fields" are "Program ID" and "Email". So, I set up a data macro behind "On INsert" on the "stakeholders" table, added a "Look up a record" block and used the following condition:

[Program ID]=[Stakeholders].[Program ID] And [Email]=[Stakeholders].[Email]

Would you be able to give me any advice? Many thanks!

Roger's Access Blog said...

I’d suggest asking your question on http://www.utteraccess.com. Several of the regulars are much more knowledgeable about them. There’s a forum specifically for Access Service (Web Databases)