Limited number of indexes (Acc97)
Jack MacDonald
existing table. Then I went to the Relationships window and tried to
enforce Referential Integrity between the two tables. Access 97
responded with an error message: "the operation failed. There are too
many indexes on the table uTblTrip. Delete some indexes and try
again."
The index window on the design view of uTblTrip table shows 14
indexes. I checked my copy of Microset Jet Database Engine
Programmer's Guide, and it states the maximum number of indexes per
table is 32, which is well above the number of indexes that I seem to
be using.
I checked in the Relationships window to see if perhaps there were
over 32 relationships, and perhaps each relationship was automatically
creating an index?? The Relationships window shows 18 direct relations
between uTblTrip and other tables in the database, so that idea
doesn't seem to hold water.
Tried repairing and compacting the database, with no success.
Then I wrote a bit of VBA code to list all the index names using DAO.
It shows there are 20 indexes, not the 14 listed in the Index window
of the table definition. I have no idea why there are six more indexes
listed via VBA than appear in the Index window???
Can anybody offer any insight? How can I enforce referential integrity
on the new table?
=======================================================
Jack MacDonald
Vancouver, B.C. Canada
Peter Russell
If you truly need more then you are in the wrong product.
I'd be looking at any 1-1 links and making sure they are necessary or
maybe even looking to split the main table and create a(nother) 1-1 link
if the relationships are context specific.
Regards
Peter Russell
PS (to another thread): Is this another reason for A2 nostalgia?
Trevor Best
<l07utu4mvafdt420d...@4ax.com>, Jack MacDonald
<jacks...@telus.net> wrote:
>I added a new table to the database and a new foreign key into an
>existing table. Then I went to the Relationships window and tried to
>enforce Referential Integrity between the two tables. Access 97
>responded with an error message: "the operation failed. There are too
>many indexes on the table uTblTrip. Delete some indexes and try
>again."
Good Advice*
>The index window on the design view of uTblTrip table shows 14
>indexes. I checked my copy of Microset Jet Database Engine
>Programmer's Guide, and it states the maximum number of indexes per
>table is 32, which is well above the number of indexes that I seem to
>be using.
>
>I checked in the Relationships window to see if perhaps there were
>over 32 relationships, and perhaps each relationship was automatically
>creating an index?? The Relationships window shows 18 direct relations
>between uTblTrip and other tables in the database, so that idea
>doesn't seem to hold water.
Releationships do automatically put indices on. 18+14 = 32, that's as
watertight as a duck's backside :-).
>Tried repairing and compacting the database, with no success.
>
>Then I wrote a bit of VBA code to list all the index names using DAO.
>It shows there are 20 indexes, not the 14 listed in the Index window
>of the table definition. I have no idea why there are six more indexes
>listed via VBA than appear in the Index window???
What code did you use? Try:
currentdb.TableDefs("tablename").Indexes.Count
I can't see how 20 came about, I even tried creating a 1-1
relationship in A2K2 and the silly thing has indexed the primary key
again.
>Can anybody offer any insight? How can I enforce referential integrity
>on the new table?
First of all, if you have indexed any foreign keys, delete those
indices, there's already an index on them, duplicate indices (and too
many) have no benefit, indeed they're detremental to performance,
aside from possibly adding confusion to the query optimiser they need
updating whenever you update/insert so those operations are slowed
down.
Check also that you don't have that annoying feature turned on that
automatically indexes fields that end in "ID", "Nun", etc. Invariably
you might call your primary key "SomthingID", then it gets 2 indexes,
the PK one and the other one just being there for laffs.
--
Dim Salary As Currency
Dim Overtime As Double
Jack MacDonald
<sith...@besty.org.uk> wrote:
>>
>>I checked in the Relationships window to see if perhaps there were
>>over 32 relationships, and perhaps each relationship was automatically
>>creating an index?? The Relationships window shows 18 direct relations
>>between uTblTrip and other tables in the database, so that idea
>>doesn't seem to hold water.
>
>Releationships do automatically put indices on. 18+14 = 32, that's as
>watertight as a duck's backside :-).
I suspected as much because of the 14+18 arithmetic... thanks for the
confirmation.
>
>>Tried repairing and compacting the database, with no success.
>>
>>Then I wrote a bit of VBA code to list all the index names using DAO.
>>It shows there are 20 indexes, not the 14 listed in the Index window
>>of the table definition. I have no idea why there are six more indexes
>>listed via VBA than appear in the Index window???
>
>What code did you use? Try:
>currentdb.TableDefs("tablename").Indexes.Count
That expression returns "20"
For the code that I mentioned earlier, I used something like:
dim db as dao.database
dim tdf as dao.tabledef
dim idx as dao.index
set db = currentdb
set tdf = db.tabledefs("tablename")
for each idx in tdf.indexes
debug.print idx.name, idx.fields
next idx
Then I simply counted the entries in the debug window = 20
>
>I can't see how 20 came about, I even tried creating a 1-1
>relationship in A2K2 and the silly thing has indexed the primary key
>again.
When I said there were 18 relationships -- what I did was to delete
all the tables from the Relationships window, then add just the target
table, then display the directly-related tables. I counted 18 tables
in the window. Further inspection (after posting the first message)
shows that some of them are really the same table (e.g. employees,
employees_1). The "utbltrip" table has three fields that pertain to
employees (primary driver, loading driver, unloading driver). Would
each of these count against the 32-index limit?
>
>>Can anybody offer any insight? How can I enforce referential integrity
>>on the new table?
>
>First of all, if you have indexed any foreign keys, delete those
>indices, there's already an index on them, duplicate indices (and too
>many) have no benefit, indeed they're detremental to performance,
>aside from possibly adding confusion to the query optimiser they need
>updating whenever you update/insert so those operations are slowed
>down.
>
>Check also that you don't have that annoying feature turned on that
>automatically indexes fields that end in "ID", "Nun", etc. Invariably
>you might call your primary key "SomthingID", then it gets 2 indexes,
>the PK one and the other one just being there for laffs.
That is the case in my db... most of the foreign keys have names
ending with ID. Recently, I've started naming my foreign keys
differently (e.g., employeeIDfk) just to avoid fieldname collision --
I guess your point about the automatic indexing is an additional
reason to avoid the "ID" suffix.
If I understand you correctly, the 18 indices from the relationships
do not appear anywhere in the Index window nor in the VBA loop shown
above. And if I delete the explict indices on these fields, then they
will continue to be indexed "internally". I will try that.
Thanks much for the insight!
Trevor Best
<q2nvtugd6dllcfnho...@4ax.com>, Jack MacDonald
<jacks...@telus.net> wrote:
If some of those relationships have the 1 side of the 1-n on that
table then no extra indices get created, it will use the PK index.
>>>Can anybody offer any insight? How can I enforce referential integrity
>>>on the new table?
>>
>>First of all, if you have indexed any foreign keys, delete those
>>indices, there's already an index on them, duplicate indices (and too
>>many) have no benefit, indeed they're detremental to performance,
>>aside from possibly adding confusion to the query optimiser they need
>>updating whenever you update/insert so those operations are slowed
>>down.
>>
>>Check also that you don't have that annoying feature turned on that
>>automatically indexes fields that end in "ID", "Nun", etc. Invariably
>>you might call your primary key "SomthingID", then it gets 2 indexes,
>>the PK one and the other one just being there for laffs.
>
>That is the case in my db... most of the foreign keys have names
>ending with ID. Recently, I've started naming my foreign keys
>differently (e.g., employeeIDfk) just to avoid fieldname collision --
>I guess your point about the automatic indexing is an additional
>reason to avoid the "ID" suffix.
No need to avoid those names, you can turn that feature off (Tools ->
Options -> Tables/Queries -> AutoIndex on import/create) just blank
that field.
>If I understand you correctly, the 18 indices from the relationships
>do not appear anywhere in the Index window nor in the VBA loop shown
>above. And if I delete the explict indices on these fields, then they
>will continue to be indexed "internally". I will try that.
The indices created by relationships will show up in the DAO listing,
just not in design view. If your VBA loop shows 20 then sure as eggs
are eggs that's how many there are. It shouldn't be refusing to put
another index on, you might try creating another index in design view
just to see if it's possible, if so then the reason for not enforcing
RI is being mis-reported (the wrong error message being displayed is
not unknown in Access) there may be some other reason for it.
John Winterbottom
news:l07utu4mvafdt420d...@4ax.com...
> I added a new table to the database and a new foreign key into an
> existing table. Then I went to the Relationships window and tried to
> enforce Referential Integrity between the two tables. Access 97
> responded with an error message: "the operation failed. There are too
> many indexes on the table uTblTrip. Delete some indexes and try
> again."
>
> on the new table?
>
>
I use DDL scripts to make any schema changes to databases, for exactly this
reason - I like to know that all the indexes, constraints etc. in a table
are those I have put there myself. It's also a good idea to explicitly name
your constraints and indexes.
Jack MacDonald
<sith...@besty.org.uk> wrote:
>
>>>
>>>First of all, if you have indexed any foreign keys, delete those
>>>indices, there's already an index on them, duplicate indices (and too
>>>many) have no benefit, indeed they're detremental to performance,
>>>aside from possibly adding confusion to the query optimiser they need
>>>updating whenever you update/insert so those operations are slowed
>>>down.
>>>
>>>Check also that you don't have that annoying feature turned on that
>>>automatically indexes fields that end in "ID", "Nun", etc. Invariably
>>>you might call your primary key "SomthingID", then it gets 2 indexes,
>>>the PK one and the other one just being there for laffs.
>>
>>That is the case in my db... most of the foreign keys have names
>>ending with ID. Recently, I've started naming my foreign keys
>>differently (e.g., employeeIDfk) just to avoid fieldname collision --
>>I guess your point about the automatic indexing is an additional
>>reason to avoid the "ID" suffix.
>
>No need to avoid those names, you can turn that feature off (Tools ->
>Options -> Tables/Queries -> AutoIndex on import/create) just blank
>that field.
Done. I wasn't aware of that feature until now.
>
>>If I understand you correctly, the 18 indices from the relationships
>>do not appear anywhere in the Index window nor in the VBA loop shown
>>above. And if I delete the explict indices on these fields, then they
>>will continue to be indexed "internally". I will try that.
>
>The indices created by relationships will show up in the DAO listing,
>just not in design view. If your VBA loop shows 20 then sure as eggs
>are eggs that's how many there are. It shouldn't be refusing to put
>another index on, you might try creating another index in design view
>just to see if it's possible, if so then the reason for not enforcing
>RI is being mis-reported (the wrong error message being displayed is
>not unknown in Access) there may be some other reason for it.
What you say makes 100% sense... but that's not how the table is
reacting.
I deleted the index that was the duplicate of the PK. Once that was
done, then I created the RI without a hitch. Then I tried creating an
index in design view on a field that I know has never been indexed
before. Access responds that there are too many indexes. At this
point, I can see 13 indices in the Index window, and there are 19
tables showing in the Direct Relationships view (plus the primary
table). That still sums to 32.
The debug window reports:
?currentdb.TableDefs("uttbltrip").Indexes.Count
20
Strange.
Anyway, I've gotten past my immediate problem and know that if another
RI is required, then I will delete another of the stray indexes.
Jack MacDonald
<sith...@besty.org.uk> wrote:
>
>The indices created by relationships will show up in the DAO listing,
>just not in design view. If your VBA loop shows 20 then sure as eggs
>are eggs that's how many there are. It shouldn't be refusing to put
>another index on, you might try creating another index in design view
>just to see if it's possible, if so then the reason for not enforcing
>RI is being mis-reported (the wrong error message being displayed is
>not unknown in Access) there may be some other reason for it.
Made a copy of the database and a copy of the table. Deleted all the
indexes and created 32 new indexes with recognizable names such as
"a", "b", etc.
tried making a 33rd index. Not allowed.
Tried adding a relation with RI - not allowed
Deleted one index
.....Indexes.count = 31
Tried adding a relation with RI - worked fine
.... Indexes.count = 32
viewed the list of indexes in Design View = 31 (as you stated)
displayed the names of all indexes using a VBA loop. Showed 32 indexes
(as you stated)
Tried repairing and compacting the database. Tried JetComp.EXE.
Neither made any difference.
Conclusion: the Indexes work transparently as you described, except
that Access will create a new index whenever RI is enforced. The index
name will be generated automatically, and will probably not match any
existing index in the table. (You probably said that already... it
just takes a while to get thru my skull)
There must be something corrupt in my database because the Design View
continues to show 13 indexes, and the ...Indexes.count continues to
show 20 and Access continues to deny adding a new index.
Thanks for the help.
TC
> On Sat, 23 Nov 2002 06:15:13 GMT, in message
> <l07utu4mvafdt420d...@4ax.com>, Jack MacDonald
> <jacks...@telus.net> wrote:
>
> duplicate indices (and too many) have no benefit, indeed they're detremental to performance
Not true. All of the index *names* are stored, but only the
non-duplicate indexes are actually created and managed by Jet.
TC
Jack MacDonald
Trying to understand all this. Here's what I did as an experiment:
1. Tools | Options: set AutoIndex to "id;num"
2. Created a new table w/ 3 fields: PeopleID, Name, CityIDfk
3. Saved table
4. Checked indexes in the Index window. 1 index named "PeopleID"
5. Created primary key. Saved table
6. Index window shows 2 indexes: new one is named "PrimaryKey"
7. ?currentdb.TableDefs("People").Indexes.Count 2
8. Removed AutoIndex entry from Tools | Options
9. Made second table with same structure. Name is People2
10. Index window shows no indexes
11. ?currentdb.TableDefs("People2").Indexes.Count 0
12. Create primary key. Index window shows 1 index
13. ?currentdb.TableDefs("People2").Indexes.Count 1
14. Made new table "City" w/2 fields CityID, CityName w/PrimaryKey
15. Related City table to People and People2 tables. No RI
16. ?currentdb.TableDefs("People").Indexes.Count 2
17. ?currentdb.TableDefs("People2").Indexes.Count 1
18. Enforced RI on both relationships
16. ?currentdb.TableDefs("People").Indexes.Count 3
17. ?currentdb.TableDefs("People2").Indexes.Count 2
18. Index window in Design Mode of both People tables continues to
show the 2 and 1 indexes respectively
19. Using vba and dao, looped through all the indexes on each table
and displayed the index name and the index field. Count of indexes
agrees with ?currentdb.TableDefs("People").Indexes.Count 3
The new indexes in both tables are named by concatening the two table
names, with the 1 side first, and the N side second (e.g. CityPeople).
The index is created on the fk in the N-side table.
20. Using the Design Mode of both the People tables. Choose the
CityIDfk field. The field properties show "NO" for Field Indexed?
21. Explicityly index the CityIDfk fields using the Field Properties.
New index is visible in the Index window, and it is named according to
the fieldName.
22. VBA code now shows two indexes in each table on the CityIDfk field
23. Remove RI from both relationships
24. VBA codes shows that the duplicate Indexes have been removed from
both tables
25. Manually add a new index to each table, using the same naming
convention as used by Access when it enforces RI on the relationships
(ie. Index name: CityPeople, field: CityIDfk)
26. Check relationships. Neither relationship is using RI
27. Try to enforce RI on the relationships. Access complains that
"Index already exists", and will not allow RI to be enforced.
28. Delete the offending indexes from both tables.
29. Enforce RI using the Relationships window.
30. Rename "People" to "PeopleRENAME"
31. VBA code shows there has been no change to the Index names
32. Open Relationships window. "PeopleRENAME" is not visible. Add it
to the display. RI is enforced on the existing relationship.
33. Remove RI from the relationship.
34. VBA code shows that the RI-generated index has been deleted.
35. Add RI to the PeopleRENAME relationship
36. VBA code shows a new index named: CityPeopleRENAME
Conclusions:
1. Access creates additional, explicitly-named indexes when RI is
used. The index is based on the foreign key of the N-table.
2. The RI indexes are hidden from the Table Design Index window. They
are also hidden from the field properties of the foreign key.
3. The RI-managed indexes count towards the 32-index limit even though
they are not visible through the Access UI. (Not shown in this set of
steps, but verified by earlier experimentation)
4. The RI indexes are named as 1-TableName N-TableName (with no
spaces or punctuation in the name)
5. RI cannot be applied if an already-existing index has the same name
as what Access wants to use. The existing index must be deleted before
RI can be enforced.
6. The RI knows which index it is based on, and does not fail if the
tables are renamed. The index name remains constant even if the tables
are renamed.
What this doesn't tell me is whether the index that's created
automatically when RI is enforced is interchangeable with an index
created explicitly on the foreign key. My guess is that the RI-managed
index is equivalent to the explicit one.
Nor does it tell me whether duplicated indexes (e.g, "PeopleID", and
"PrimaryKey") provide any benefit or cost.
If I understand TS's comment correctly, the fact that there are two
index names has no effect on performance, because only one index is
actually created and managed. However, the duplicate names will count
towards the 32-index limit.
Trevor Best
<3964f176.02112...@posting.google.com>,
ke...@aussiemail.com.au (TC) wrote:
Do you have a reference for that? I can seek on all indices on a table
without it complaining.
CDB
for the two-table indexes?
Clive
"Jack MacDonald" <jacks...@telus.net> wrote in message
> ...
Trevor Best
<v950uus72gvnu9fee...@4ax.com>, Jack MacDonald
<jacks...@telus.net> wrote:
>What you say makes 100% sense... but that's not how the table is
>reacting.
>
>I deleted the index that was the duplicate of the PK. Once that was
>done, then I created the RI without a hitch. Then I tried creating an
>index in design view on a field that I know has never been indexed
>before. Access responds that there are too many indexes. At this
>point, I can see 13 indices in the Index window, and there are 19
>tables showing in the Direct Relationships view (plus the primary
>table). That still sums to 32.
>
>The debug window reports:
>?currentdb.TableDefs("uttbltrip").Indexes.Count
>20
>
>Strange.
Even stranger, 32 is 20 hex (insert Twilight Zone music here)
;-)
Jack MacDonald
>Jack, are you looking at MSysRelationships table - which holds the specs
>for the two-table indexes?
>
>Clive
>
>
No I haven't. Not sure what to glean from that table???
Jack MacDonald
<sith...@besty.org.uk> wrote:
>>
>>The debug window reports:
>>?currentdb.TableDefs("uttbltrip").Indexes.Count
>>20
>>
>>Strange.
>
>Even stranger, 32 is 20 hex (insert Twilight Zone music here)
>;-)
Nice try ;-) Unfortunately, I have found a more mundane explanation.
Opened the Relationships window and deleted all the relationships in
which the utblTrip was on the "1" side. In other words, the
relationships in which the PK of the utblTrip participates. Confirmed
100% that there were NO relationships visible in which the PK
participated.
Opened the table in Design View. Created bogus indexes until no more
could be added. Checked the number of indexes via ....Indexes.Count.
There are 30 indexes on the table.
Opened the Relationships window. Aha!! There are now two relationships
visible in which the PK participates. Delete them.
Return to Design Mode. Successfully add two more indexes. Total is now
32.
So it appears that the Relationships in which the PK participates
somehow got duplicated, which somehow increased the count of indexes
beyond what was visible.
Using what I've learned thru this hassle, I've reduced the total
number of indexes to 13:
PrimaryKey
two arbitrary indexes used for searching
(only these three are visible via Index window in Access UI)
10 foreign keys that have RI enforced -- these are created
automatically by Access and do not appear in the Access UI.
A far cry from the 32 that Access complained about originally!
Then I tried adding and deleting an explicit index on a foreign key
and observing the results of a query that uses the foreign key. I used
the "ShowPlan" option as explained in the Access Developers Handbook.
If an explicit fieldname index exists, then it is used by the
optimizer. If it doesn't exist, then the optimizer uses the Index that
was created automatically when RI was enforced.
So it appears to make no difference to the query optimizer whether or
not you create an explicit index on the foreign keys. But they do
count against the 32 index maximum.
One last piece of confusion... the Showplan.out file makes reference
to Rushmore when an explicit fieldname index is present, but not when
the explicit fieldname index is missing. I have no idea whether that
influences the query efficiency.
cafe
I don't have a reference, but here's how it works.
The jet data structure includes various entries for storing index
information. (By "index information" here, I do not mean B-trees, leaf
nodes, data page pointers & so on. I mean index names, column definitions,
ascendency orders & so on.) Some of those entries occur once per DEFINED
index. An example is, the entries that store the index names. Other entries
only occur once per DISTINCT index. An example is, the entries that define
the index column definitions. (By DISTINCT index here, I mean, an index that
is different - in some manner other than just its name - to every other
index on the table.) A further structure implements a many-to-one
relationship between the DEFINED entries, & the DISTINCT ones. I'm talking
here of what happens at the jet file level, not what is exposed via DAO.
So, if you defined 20 duplicate indexes, there would be 20 DEFINED entries,
1 DISTINCT entry, and each DEFINED entry would point to that DISTINCT entry.
And here's the rub: only the DISTINCT entry(ies) create actual index "files"
(for want of a better term) within the database. So in this example, there
is only a single index "file" within the database. The 19 duplicate indexes
all use that same index "file". The duplicate indexes have no overhead, save
for the few bytes used for thier names & other similar details.
DAO then exposes all the indexes by joining the DEFINED & DISTINCT entries
as appropriate. Thus, duplicate indexes *look like* they are seperate
indexes - and you can operate them independently - but in fact they *do not*
have duplicate index "files" within the database.
When I saw this at first, I thought it was weird. Then I realized it was
very clever. Say the user creates a relationship. Access wants to create
some indexes to support that relationship. What to do if the indexes in
question already exist? On the face of it, there are only two options: (1)
create duplicate indexes, & put up with the overhead; or (2) use the user's
indexes, and put up with the fact that the relationship will fall apart if
the user then deletes his (own) indexes. However, given the optimization
described above, Access can forget about the impact of any duplication!
There is *no* significant impact from index duplication. Access can happily
define duplicate indexes, to support the relationship. It can name those
indexes as it chooses. If the user then deletes his *own* indexes, this does
*not* delete the corresponding index "files", because Jet can see that there
are still some DEFINED entries pointing to those files.
Very cool design indeed.
HTH,
TC
TC
This is a duplicate of one that I posted from "cafe".
Trevor Best <sith...@besty.org.uk> wrote in message news:<3t22uusl13boketha...@4ax.com>...
CDB
in the Relationships window, or by SQL DDL or by DAO.
I created a report so that the actual specs could be checked, and always run
that before releasing an update. Weird things have been observed, similar to
what you are reporting.
An enforced relationship is a unique index across two tables, referenced
from the "child" table.
Don't look at the sometimes erratic relationships window - go to its source.
Clive
"Jack MacDonald" <jacks...@telus.net> wrote in message
news:d3q2uuoqb87c2mt5h...@4ax.com...
rkc
"CDB" <alpha@-wave.co.nz> wrote in message news:arr7le$t63$1...@news.wave.co.nz...
> Jack, are you looking at MSysRelationships table - which holds the specs
> for the two-table indexes?
One of the properties of the Index object is .Foreign which indicates whether
or not it represents a foreign key so there is no need to get relationship info
from anywhere else.
John Winterbottom
> That's where you get the specifications of the Relationships created
either
> in the Relationships window, or by SQL DDL or by DAO.
>
> I created a report so that the actual specs could be checked, and always
run
> that before releasing an update. Weird things have been observed, similar
to
> what you are reporting.
>
> An enforced relationship is a unique index across two tables, referenced
> from the "child" table.
I think it's a Constraint not an Index. AFAIK you need to explicitly create
an index if you are doing this with DDL. At least that's how it works in SQL
Server.
Access may automagically add both the foreign key constraint *and* an index
on the foreign key when you create the relationship in the relationships
window.
John Winterbottom
"rkc" <r...@spamfree.rochester.rr.com> wrote in message
news:6FgE9.97068$Em.30...@twister.nyroc.rr.com...
I thought that a FK relationship was defined as a constraint, and that you
had to manually add an index on the FK if you wanted one.
But I've just tested it with DAO and ADOX - and both of them list the FK
constraint in the tables indexes collection. In fact there doesn't seem to
be any such thing as an ADO or DAO constraint.
-------------------------
CREATE TABLE foo (fid INT NOT NULL PRIMARY KEY)
CREATE TABLE bar (bid INT NOT NULL PRIMARY KEY,
fid INT NOT NULL,
CONSTRAINT FK_bar_fid FOREIGN KEY (fid)
REFERENCES foo(fid))
--------------------------
--------------------------
Sub listIDX_DAO()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("bar")
For Each idx In tdf.Indexes
Debug.Print idx.Name
Next idx
End Sub
Sub listIDX_ADO()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As ADOX.Index
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("bar")
For Each idx In tbl.Indexes
Debug.Print idx.Name
Next idx
End Sub
------------------------------
Trevor Best
<3964f176.0211...@posting.google.com>,
ke...@aussiemail.com.au (TC) wrote:
>Have reposted this because I'm not sure if the one I posted from "cafe" got through.
>
>This is a duplicate of one that I posted from "cafe".
The one from "cafe" got through, thanks, very enlightening.
rkc
"John Winterbottom" <jo...@assaynet.co.m> wrote in message
news:FphE9.29824$lj.6...@read1.cgocable.net...
>
> "rkc" <r...@spamfree.rochester.rr.com> wrote in message
> news:6FgE9.97068$Em.30...@twister.nyroc.rr.com...
> >
> > "CDB" <alpha@-wave.co.nz> wrote in message
> news:arr7le$t63$1...@news.wave.co.nz...
> > > Jack, are you looking at MSysRelationships table - which holds the
> specs
> > > for the two-table indexes?
> >
> > One of the properties of the Index object is .Foreign which indicates
> whether
> > or not it represents a foreign key so there is no need to get relationship
> info
> > from anywhere else.
> >
>
>
> I thought that a FK relationship was defined as a constraint, and that you
> had to manually add an index on the FK if you wanted one.
>
> But I've just tested it with DAO and ADOX - and both of them list the FK
> constraint in the tables indexes collection. In fact there doesn't seem to
> be any such thing as an ADO or DAO constraint.
In DAO they are reflected in the Relation object of the Relations collection
of the Database object. Same information as in the MSysRelationships table
mentioned by Trevor.
I thought I read recently in the a 2002 help file that an index was created on
a foreign key field(s) when referential integrity was enforced, but I guess I
remember wrong since that's not reflected in the DDL you used.
Trevor Best
<_IoE9.99305$Em.31...@twister.nyroc.rr.com>, "rkc"
<r...@spamfree.rochester.rr.com> wrote:
>I thought I read recently in the a 2002 help file that an index was created on
> a foreign key field(s) when referential integrity was enforced, but I guess I
>remember wrong since that's not reflected in the DDL you used.
you must remember right because I can see the index that the RI
creates using DAO.
Jack MacDonald
out that's how it worked. Much appreciate your lucid explanation and
clarification.
Thanks to all that helped me with the index problem.
TC
> After all the spelunking that I did via the Access UI, I kinda figured
> out that's how it worked.
And I might say, an impressive piece of spelunking it was. Lots of people
ask questions, but few do the hard yards necessary to actually work things
out for themselves. Although I didn't read your spelunk in detail (because
it's not relevant to me at present), I actually saved a copy for possible
future reference.
Re. duplicate indexes, I used to write code to delete them from production
databases. It was part of my checklist for releasing new versions. But
knowing what I know know, I've dropped all of that, because it isn't worth
the effort - unless, of course, the number of duplicate definitions begins
to impact on your ability to add new (non duplicate) definitions!
TC