Too many indexes?? (1 Viewer)

[Cowlers]

Hi All,

I have a table within which I store my "Warehouses". The services offered by each warehouse (there are 8 of them) are stored in here as yes/no fields as well as the full addresses of each warehouse. For one warehouse I can therefore say if it contains "Stock" or offers a "Painting" facility etc. This allows me to restrict my drop-downs in the forms where I generate stock movements or painting orders to only those warehouses.

I have set up relationships from this table to the various other tables that use the WHID field within them (I thought this was the correct way so that if someone attempts to delete a warehouse it will not let them if a record exists elsewhere that refers to that warehouse). I have just tried to add a new relationship to a new type of order and now I am receiving an error because Access says I have too many indexes against this warehouse. My problem with this is that if I click on the table and view indexes I can only see about 5 and my understanding is that the limit is 32...

Please could someone point me in the right direction to resolve this issue?

Many thanks,

Dan

 

[Cowlers]

Thank you for your reply Access Blaster,

The strange thing is that I have only added indexes to a couple of fields myself, all the others seem to have been added automatically for me I guess as a result of adding relationships with referential integrity. My bigger problem as I outlined in my original post is that I can't seem to see where all these indexes are which means I have no way of checking to see which ones are actually relevant to me and delete some of them so I can create the extra relationships I require!

Regards,

Dan

 

[gemma-the-husky]

indexes are added automatically for fields ending in terms like ID or Code

The "autoindex" terms that access uses are somewhere in the tools/options, and you can edit/turn them off if you want

 

[Cowlers]

I have 52 tables in my db,

the "tblWarehouses" table is the one that I am getting the warning about. This has 15 fields but only the ID field and 4 other fields are indexed according to the properties.

Should I also consider the tables which use the ID field of this table as a foreign key as well?

Many thanks,

Dan

 

[Cowlers]

Ok, have done a bit more examination and think I may have found my problem - not that I know how to solve it!!

Although the explicitly defined indexes are only the 5 listed above I also have checked the relationships that this table has with other tables.

In total there are relationship lines between the ID field in tblwarehouses and 26 other tables in my database (and I need to add more!). My guess is that with the 26 links, plus the 5 indexes listed above gives me 31 and I was trying to define two new links at the same time when my error came up so that would push it to 33 which is above the 32 limit??

The reason for so many links is because I am trying to be clever and define one record for a warehouse with tick boxes to define if it falls within a variety of categories (which allows me to build drop-downs etc. on the fly). I guess what I could do is to separate this out so that each checkbox becomes a new table which defines which warehouses are in that category and I can therefore link to these sub-tables instead of the main tblwarehouses one. If I do this I should be able to spread the various relationships between these sub-tables but will this overcome my issue above?

Many thanks,

Dan

 

[Cowlers]

Hi again,

I've overcome this issue now, I have set up some 1 to 1 tables from my original table and now the most relationships I have between any two tables in my database is 9 which is much more healthy than the 38 I was going to require!!

Thank you for your help folks, just got to go through my db and get rid of the niggles this change has made,

Best regards,

Dan

 

[gemma-the-husky]

I see

the other thing that access does is automatically creates a hidden index for the many side of linked table in a relationship.

if you examine the properties of the indexes using dao

for each idx in currentdb.tabedef("sometable").indexes
etc the hidden indexes will have an attribute of foreign (and a name something like "TableA-TableB")