[AccessD] Relationship and hidden index (was: Error in Access 2003 that I've never seen before)

Hi John

>>> jwcolby at colbyconsulting.com 06/25 11:49 pm >>>
If this is an Access container you are discussing, any field used in a
relationship has an index created on that field by the Jet engine as
the
relationship is created.  This index is hidden.  It simply cannot be
seen in
any way that I am aware of.  Thus if you are discussing a field like
this,
there is likely that hidden index still in existence.

---

If not seen, they can be listed.
Here's an old posting of mine:

From: 	Gustav Brock
To:	AccessD at databaseadvisors.com 
Date: 	2001-09-04 11:40:00
Subject: 	Re: [AccessD] indexes in keys

Hi Susan and Mike

> I'm a little confused about indexes where keys are concerned. Jet
> indexes keys -- primary and foreign. When does it index a foreign
key
> and is this an internal function as there's no evidence in the
table's
> Indexed property. I'm assuming it's a temporary index, created as
> needed. Is this correct?

No, the index is not temporary.

I hate to quote myself but this time it is necessary.

Way back - on the old MT Group list - I posted the message below. The
problem is that, although the article referred to can still be found
on
the MS site, it has been edited down and all the notes (the real
stuff)
are gone.

The article has moved, and now is found at:

http://support.microsoft.com/support/kb/articles/Q116/1/45.asp 

The issue is, as Mike states, the hidden indexes. If you move along as
the disciplined developer doing as you have learned, creating indexes
then relations, you end up with duplicate indexes.
>From an advanced user's view this may be a nice feature with
automatically created indexes when needed, but why hide these? Stupid.

Anyway, here's the quote from 1999-09-04, exactly two years ago:

<quote>

Hi Larry,

> .. take a look at MSKB article Q116145 "Create and Drop Tables
> and Relationships Using SQL DDL"

http://technet.microsoft.com/cdonline/default-f.asp?target=http://technet.microsoft.com/cdonline/content/complete/srvnetwk/SQL/Technote/msjet/jetch03.htm


I did that, but I don't think this is what John is looking for:

Setting a referential integrity foreign key constraint is the only form
of
relationship that can be established through SQL DDL. To set other
Relation
object types, such as cascading updates or cascading deletes, you must
create
the Relation object through DAO.


Further, the article states as we might suspect:

There is no way to modify an existing relationship. You must delete
the
relationship and then re-create it.


NB: Finally it states this very interesting detail which I wasn't aware
of:

In the SQL DDL example shown earlier in this chapter, creating a new
relationship between two tables involves creating an index on the
foreign key
field. When you create a new Relation object by using DAO, Microsoft
Jet
automatically creates an index on the foreign key field or fields, even
if one
already exists. If you create a relationship between two tables with
referential
integrity enforced, avoid creating an index on the foreign key, as it
will be a
duplicate index. The foreign key index is necessary for enforcing
referential
integrity, so don't delete it. The index that's created on the foreign
key when
you create a relationship appears in the Indexes collection of the
foreign
table.

I can confirm this!
This simple function (no copyright stated) created for the purpose:

Public Function ChkIndexes()

  Dim dbs As Database
  Dim tdf As TableDef
  Dim intI As Integer
  Dim intF As Integer

  Set dbs = CurrentDb()
  Set tdf = dbs.TableDefs("tblchild")

  For intI = 1 To tdf.Indexes.Count
    With tdf.Indexes(intI - 1)
      Debug.Print .Name
      For intF = 1 To .Fields.Count
        Debug.Print , .Fields(intF - 1).Name
      Next intF
    End With
  Next intI

End Function

- returns:

PrimaryKey
              ID
Sekunda
              FK
tblParenttblChild
              FK


Creating a relationship creates an index given a name from
concatenating the
names of the parent and the child table.
However, this - the third index here, tblParenttblChild - is not
showing up when
opening the table in design view!

So: How many of us are dealing with hidden duplicate indexes??

</quote>

/gustav