Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    'Too many indexes' message (97 & 2000)

    I seem to have hit a wall here. Supposedly a table can have 32 indexes, but I am getting the "too many indexes" message way before that. Take this scenario, I have this table that:
    - is on the "many" side of 2 one-to-many relationships (RI enforced).
    - has a PrimaryKey
    - has 14 other indexes manually selected.

    So this gives me a total of 17 indexes. This same table is also the "one" side of a bunch of one-to-many relationships. So, I start creating these relationships in the relationship window. I can define 9 of them, but trying to create the 10th gives me the "too many indexes" error. If I try to add another index to this table, I will get the same message. If I delete an index, I can add another relationship, or I can delete a relationship and add another index. Apparently the total of indexes + relationships is equal to 26!

    I just don't understand this at all. Access doesn't create an index on the "one" side when you establish a one-to-many relationship with RI. Yet something is going on. I converted my database from A97 to A2000, and problem persists. I couldn't find anything relevant in MSKB searching for "too many indexes".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Too many indexes' message (97 & 2000)

    It looks as if the limit is from the query specification.
    from help for "Microsoft Access database query specifications"
    ... Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships

    Peter

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Too many indexes' message (97 & 2000)

    <P ID="edit" class=small>(Edited by WendellB on 20-Mar-03 13:20. Note simultaneous post by Bat17)</P>This is pretty much SWAG, but is it possible that Access is creating an index for each relationship you create, in addition to any that already exist on the table. I don't pretend to understand the underlying structure of relationships, but there must be a mechanism something like that for doing referential integrity checks.

    Well done Peter - looks like you nailed it.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: 'Too many indexes' message (97 & 2000)

    Silly me! I kept looking under Table Specifications, not Query Specifications!

    Thanks!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •