MSQuery: "Illegal Duplicate Key" After Creating Index

• 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

• 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.

  -or-

• 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.

REFERENCES

For more information about the Create Index Dialog Box, choose the Search button in Help and type:

    creating indexes