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

Thanks Stuart. It's weird, so many times I open my tables and see indexes
that I did not even create, useless pk names that are duplicates of the
(quite sufficient) primarykey index, and the occasional odd FKBlahBlah index
which I myself did not create (or was not substance-free if/when I did), and
I go ahead and delete them. But the one time I know there ought to be a real
index - created by MS Functionality - they go ahead and make it visible...
and there does not even appear to be a property I can manipulate to make it
visible?? I have to wonder then, what makes some indexes visible and others
not, and where this property is accessible if at all...

Thanks again!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Sunday, November 30, 2014 6:23 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?

It has always been thus!  The index really is "hidden". :(

On 30 Nov 2014 at 15:40, Bill Benson wrote:

> 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).N
> ame
> 
> 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
> 


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