Tuesday, May 18, 2010

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

This article has moved: Access 2010 Web Databases: How can I create a Compound Unique Index?

10 comments:

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.

http://www.utteraccess.com/forum/index.php?showforum=74

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.
thankyou

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)
http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html