Microsoft Access table indexes are limited to 32. Can this be changed

I've added two more table to our database backend and when I try to add the indexes from one table to the other, I get the message that "there are too many indexes on the table.  
I deleted two indexes from two other tables, but when I tried to add the indexes to the new tables it still fails.

So my questions is why is it still failing?
Can the 32 indexes problem be bypassed?
J.R. SitmanIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I assume by "from one table to the other" that you mean relationships, not indices.

What version of Access are you using?

Can you provide us with a screen shot of your relationship diagram?
0
Rey Obrero (Capricorn1)Commented:
try doing a compact and repair after deleting indexes, then add the new indexes
0
J.R. SitmanIT DirectorAuthor Commented:
Yes, relationships.  See attached.
relationships.png
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Dale FyeCommented:
I'd try C1's recommendation first.

Then try it again and if you still get the error, reposition the error message box and the edit relationships dialog so that we can see your complaints table in the diagram.
0
J.R. SitmanIT DirectorAuthor Commented:
I'll post tomorrow. Database in use.  Is there no way around the 32 index limit?
0
Dale FyeCommented:
cannot imagine why you would need more than 32 indices on a single table.

Can you take a screen shot of your relationships diagram, at least the part that shows your Complaints table and all of its relationships?
0
Rey Obrero (Capricorn1)Commented:
<  Is there no way around the 32 index limit? > sorry, but that is the limit in access

if it is really necessary to have that many indices, my suggestion is to upgrade to SQL server
0
J.R. SitmanIT DirectorAuthor Commented:
here is the relationship screen shot.  I guess maybe I've designed something incorrectly.  Not sure how much you can tell from this since you can't see all the relationships to the tables.
complaints.png
0
Rey Obrero (Capricorn1)Commented:
did you try doing a compact and repair after deleting indexes ?
0
J.R. SitmanIT DirectorAuthor Commented:
I can't do it until all the users are out
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The limit cannot be changed.  32 is it.

 Be aware that if you create a relationship and enforce RI, JET automatically creates a hidden index on the many side of the join.

 So you have one of two choices:

1. Don't enforce RI.

or

2. Don't index the join key yourself.

 That will cut down on the indexes.   If you still bump into the 32 index limit, then your only option is to convert the back end to something like SQL Server.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
why not copy the db to your desktop or laptop and play with the copy first.
0
J.R. SitmanIT DirectorAuthor Commented:
Good idea.  I'll test a separate copy of the backend.  I'll post later
0
J.R. SitmanIT DirectorAuthor Commented:
Maybe I'm over thinking what I need to do.  We handle criminal Animal cruelty cases.  In the new form(s) I creating, I've created additional tables to track items that can have multiples.  Such as multiple case codes, multiple attorneys, animals, etc.  Each one of these table I've linked to the Complaint table by the complaintNo field.

Is this correct or do I not need to link them?  If I'm doing it correctly then what one of you suggested was not to Enforce integrity.  Is that correct?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Such as multiple case codes, multiple attorneys, animals, etc.  Each one of these table I've linked to the Complaint table by the complaintNo field. >>

  Without seeing it, can't say for sure, but yes that sounds like a proper relational design.

<<If I'm doing it correctly then what one of you suggested was not to Enforce integrity.  Is that correct? >>

  That was correct.  When you enforce RI, you're making sure that:

1. You can't add a child record without a parent.
2. You can't delete a parent without getting rid of the child records first.

 If you don't enforce RI, then you can do both of those.   In most cases, that's not really a problem because what you've done in development doesn't allow that to happen anyway.

 Personally, I would skip the RI.

Jim.
0
J.R. SitmanIT DirectorAuthor Commented:
Thanks.  Just to verify if I skip the RI then I won't get the exceeded 32 index error, correct
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thanks.  Just to verify if I skip the RI then I won't get the exceeded 32 index error, correct >>

Most likely yes, because the hidden indexes won't be created.   Your not modifying the limit.

If you try to define a 33rd index on your own, your still going to get the error.

Jim.
0
J.R. SitmanIT DirectorAuthor Commented:
Thanks for all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.