Microsoft KB Archive/121599

{|
 * width="100%"|

MSQuery: "Illegal Duplicate Key" After Creating Index

 * }

-

The information in this article applies to:


 * Microsoft Query for Windows, version 1.0

-

SYMPTOMS
In Microsoft Query, if you use a dBASE, Microsoft FoxPro, or Microsoft Access data source to create a unique index using an index field that does not contain unique data, the index is created successfully; however, when you close the Create Index dialog box, you receive the following error message:

Illegal duplicate key

When you receive this error message, any of the following may have occurred:


 * The table you saved was not created. This behavior occurs when you use the version of Microsoft Query that ships with Microsoft Excel version 5.0.
 * The table you saved was created, but the table only contains the first records that do not have duplicate entries in the field that you used to create the index. This behavior occurs when you use the version of Microsoft Query that ships with Microsoft Excel version 5.0c.

CAUSE
This behavior occurs because, when you create an index in the process of saving a new table, the index is created before the data is inserted into the table. Because of the order of this process, the data source does not know whether a field is unique or not. It is possible then to create a unique index on a field that is not unique.

WORKAROUND
To avoid these problems, only create a unique index for a table when you are certain that the index field that you select uniquely identifies the records in the database.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.