Microsoft Access Query Index Error Message

[MicahAtTgt]

Greetings!
I am attempting to write a Select Access query (one table, no joins, etc.) and when I attempt to save the query (or any query) when using this database, I receive the following Microsoft Access error message: "The following changes to the table were not successful becuase they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data , remove the index, or re-define the index to permit duplicate values." I am confused because the query isn't writing back to the database so it shouldn't be trying to create duplicate records. To take it a step further, I removed the index from the table, re-defined the field that is set to not allow duplicate values and tried it again and I still receive the error message....I'm dumbfounded! Any ideas? Thank you in advance for your help!

 

[Golom]

Access is probably talking about one of its system tables, not your tables since this is happening when you try to save the query, not, I gather, when you run it. I suggest that you compact the database.

 

[willir]

MicahAtTgt

This is a message you get when attempting to update / insert a record that breaks the properties for the table as defined during the table design.

For example
- write a record with a duplicate primary key
- change an existing primary key to a primary key already used.

But the primary key is not the only concern or point of failure. Check your table setup and look for fields with "no duplicate".

For a better view of the indexes, when in the table design mode, click on the menu item "View" and select indexes. Review the indexes for look for...
- unique set to yes
- ignore nulls set to no

Dont change this unless you know that you have to. But this will possibly identify your issue. For example, using the invoice example, a unique property may have been set for InvoiceNo + InvoiceDetailNo fields where both the invoice number plus the invoice detail number have to be unique. A1000 + 0010. This is just an example since I do not have youre data. OR the field for InvoiceDetailNo may be set to not allow for nulls.

Still have not found it?
It is considered good design by most when referential integrity for relationships is enforced. What this means that before creating a child record, the parent record must first exist. Specicially, before you can create an invoice detail record, the invoice header has to exist.
- Is a foreign key missing when trying to create the record? The foreign key would be the primary key used from the parent table to link the parent and child.

It would have been useful to see what SQL statment you are trying to run.

If you are still having problems, post your SQL statement for us to peruse.

Richard

 

[MicahAtTgt]

Thank you for your suggestions! Running a compact on the database has made the error message go away...sweet!
Thanks again