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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, relationships. See attached.
relationships.png
relationships.png
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.
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.
ASKER
I'll post tomorrow. Database in use. Is there no way around the 32 index limit?
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?
Can you take a screen shot of your relationships diagram, at least the part that shows your Complaints table and all of its relationships?
< 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
if it is really necessary to have that many indices, my suggestion is to upgrade to SQL server
ASKER
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
complaints.png
did you try doing a compact and repair after deleting indexes ?
ASKER
I can't do it until all the users are out
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why not copy the db to your desktop or laptop and play with the copy first.
ASKER
Good idea. I'll test a separate copy of the backend. I'll post later
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Just to verify if I skip the RI then I won't get the exceeded 32 index error, correct
<<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.
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.
ASKER
Thanks for all the help.
What version of Access are you using?
Can you provide us with a screen shot of your relationship diagram?