[AccessD] Why isn't an index created in the referencing table - or why can't I discover it in design view?

Hi Bill

No, this is not a bug. Except if you mean that creating a hidden index even if an identical and visible index already exists is a bug.
It has been like this "always". Don't forget that Access is for end users.

/gustav

________________________________________
Fra: accessd-bounces at databaseadvisors.com <accessd-bounces at databaseadvisors.com> på vegne af Bill Benson <bensonforums at gmail.com>
Sendt: 30. november 2014 21:40
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Why isn't an index created in the referencing table - or why can't I discover it in design view?

Oh now this is interesting. The documentation says ". creates . in the referencing table" and so I decided to skip design view and go right the immediate window to test

?currentdb.tabledefs(currentdb.Relations(2).ForeignTable).Indexes(1).Name

Result:   Table1Table2

. which is the name of the relationship showing in MSysRelationships.

So the issue is not that no index is created - it is that the index created is not visible!  I have closed the database and re-opened it and no matter what, am not able to see Table1Table2 in the Indexes view for Table2, but can see that relationship in the MSysRelationshipstable, with szObject = Table2, can see the relationship in the Relationships window, and can access it through Table2's Indexes collection.

This appears to me to be a bug in the Design window.

From: Bill Benson [mailto:bensonforums at gmail.com]
Sent: Sunday, November 30, 2014 3:25 PM
To: 'Access Developers discussion and problem solving'
Subject: Why isn't an index created in the referencing table - or why can't
I discover it in design view?

I am probably going to have more than one question while poring over this document:

http://msdn.microsoft.com/en-us/library/bb177503.aspx

My first is that I cannot seem to validate the 2nd of the two sentences in this sentence pair:

"When you enforce referential integrity, a unique index must already exist for the key field of the referenced table. THE MICROSOFT ACCESS DATABASE ENGINE AUTOMATICALLY CREATES AN INDEX WITH THE FOREIGN PROPERTY SET TO ACT AS THE FOREIGN KEY IN THE REFERENCING TABLE.

I have tried this repeatedly in the Relationships window with Table1 and Table2, where Table1 is the referenced table having pk Table1ID, and trying to set the relationship by linking that key field to the field Table2.FKTable1_ID, with referential integrity. I look in the design view, Indexes, for Table2 (the referencing table) and I do not see any indexes created there.