Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to increase indexes on a Table? (access 2000)

    I get an error message that there are too many, and it wants me to delete one of the relationships before allowing me to establish another. How do I increase the number of allowed relationships?

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

    Re: How to increase indexes on a Table? (access 2000)

    A table in Access 2000 and 2002 is limited to 32 indexes on that table, and any one index can have a maximum of 10 fields. Those limits cannot be increased. Note that when you design a table, Access wants to automatically put an index on any field that is an integer, so you may have a number of fields where there indexes that you don't really need from a performance perspective - and that's the only reason for having an index. Hope this helps.
    Wendell

  3. #3
    Lounger
    Join Date
    Aug 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to increase indexes on a Table? (access 2000)

    So how do I remove the index without removing the relationship?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to increase indexes on a Table? (access 2000)

    Open the table in design view and click on the "indexes" icon on the toolbar (next to the primary key icon on my toolbar). Highlight the row corresponding to the index you want to delete and press the delete key.

    HTH.
    Carol W.

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

    Re: How to increase indexes on a Table? (access 2000)

    WSC has indicated how to remove indexes, but it appears you may be confusing indexes with entity relations. You can set a relational integrity constraint without having an index, though in many cases it would be very desirable from a performance perspective. So which are you trying to add, a relationship in the ER diagram view, or an index on a table? If you really have a table with 32 ER constraints, then we should probably look at your table design as it probably has been over-normalized. Perhaps if you gave us an idea of the tables involved in your database it would help us to better understand your problem.
    Wendell

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

    Re: How to increase indexes on a Table? (access 2000)

    As Wendell sugguested, you seem to be confusing Indexes with relationships. When you create a new field in a table, Access may automatically create an index for this field based on the setting in the AutoIndex option in the the database options. So if a field contains key phrases such as ID, No, Name, Num, or several others, you automatically get an index. This in itself isn't so bad, except that if this is a foreign key this is on the "many" side of a one-to-many relationship with referential integrity enforced, then Access creates a separate index on this field to enforce this relationship. This means you often have 2 indexes for many fields!

    My advice would be to first delete all the entries in the AutoIndex property so this won't happen again. Then, open your table in design view and delete all indexes for fields that are foreign keys to another table (if referential integrity is enforced in that relationship). Then compact database. YOu don't have to touch the relationships.
    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
  •