Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit number of indexes (A97 SR2)

    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 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 uTblTrip table shows 14 indexes. The Relationships window shows 18 direct relations between uTblTrip and other tables in the database. 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.

    Tried repairing and compacting the database, with no success.

    Can anybody offer any insight? How can I enforce referential integrity on the new table?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit number of indexes (A97 SR2)

    The relationship window shows relationships, not indexes.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit number of indexes (A97 SR2)

    Point taken <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I didn't make it clear why I was looking at the Relationships window while asking about indexes. I had checked the Relationships window originally to see if perhaps there were over 32 relationships, and perhaps each relationship was automatically creating an index?? But there were fewer than 32 relationships, so that idea doesn't seem to hold water.

    Meanwhile, 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???

    My original question still stands: why is Access preventing the enforced Referential Integrity? Is *says* the reason that it cannot create the relationship is too many indexes, yet I can find only 14 (or is it 20?), and AFAIK, the Jet 3.x specification limit is 32 indexes per table.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit number of indexes (A97 SR2)

    There is a rather wierd behavior in Access where it automagically creates indexes on any field with a name that contains ID, key, code, or num unless you have the AutoIndex on Import/Create turned off. You might check and see if that could be biting you. Also, if you have combobox lookups set up on any of your fields, that may be creating some virtual indexes, although that's speculation on my part.
    Charlotte

Posting Permissions

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