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

Bill,

 As the others have said, this is not a bug and it is hidden.  Where it
usually gets you is when you hit the 32 index limit.

 It's always nice to be able to walk in and get measurable gains without
doing much other than deleting the duplicate indexes.

 Also note that it is only if you enforce RI.   Doing a relationship alone
doesn't do it.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Sunday, November 30, 2014 03:41 PM
To: 'Access Developers discussion and problem solving'
Subject: 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.

 

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com