Fix a Microsoft Access Key Violation When Importing Excel Data
Question details
The user needs to identify and correct source data that prevents Excel records from being imported into Microsoft Access.

- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- Windows 7 on one test computer; the other computer's operating system was not provided.
- Scenario
- Importing an Excel file generated by another system into an Access table and comparing the results on two computers.
- Observed behavior
- An import reports a key violation. The reported resolution involved correcting a malformed source row; the successful Windows 7 test had used a different dated source file.
Confirm that both computers use the same Access database, destination table, and exact Excel file, including its date and worksheet. Note whether the import appends records to an existing table or creates a new table, and record the exact warning text.
Check primary keys and unique indexes for conflicting records
Use this approach when Access explicitly reports key violations, especially when appending Excel rows to an existing table.
A key violation means an imported record conflicts with a primary key or another unique index. Duplicate values can occur within the Excel file or between the incoming rows and records already in Access.
In the Access Navigation Pane, right-click the destination table and select Design View. Identify the field or fields marked with a key icon, then open Table Design > Indexes to check other indexes with Unique set to Yes.
Open the destination table in Datasheet View and open the source worksheet in Excel. Compare the primary-key and unique-index values in both places. For a multi-field key or index, compare the full combination of values rather than each field separately.
In Excel, select the relevant key column and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For a multi-field key, review rows with matching combinations across all participating columns.
Correct erroneous key values or exclude records that are already present, according to your data rules. Save and close the workbook, then in Access select External Data > New Data Source > From File > Excel and retry the appropriate import option. In older versions, select External Data > Excel.

Inspect rejected Excel rows for malformed or missing values
Use this approach when the key checks do not explain the failure, or the import also reports missing values or type-conversion errors.
Review Excel Source Data with WPS Spreadsheet
WPS Office is a free, lightweight alternative for reviewing and editing the Excel source workbook before an Access import. Its familiar interface and support for Excel formats make migration straightforward, but it does not repair Access database constraints or replace Microsoft Access.

Frequently Asked Questions
Does an Access key violation mean I do not have permission to import?
No. A key violation concerns table constraints, not account permissions. Right-click the destination table in Access > Design View, then inspect its primary key and Table Design > Indexes for unique indexes.
Why does the import work on one computer but fail on another?
First verify that both computers use the exact same Excel file, worksheet, database, and existing destination records. In this case, the Windows 7 test used a different dated source file, so it was not an equivalent comparison.
Can changing the Excel cell format fix the import error?
Only if the underlying data is also corrected where necessary. In Excel, inspect and edit the actual cell value; a Number or Date display format alone may leave a text value unchanged. A duplicate primary key still requires resolving the duplicate, regardless of formatting.
What if the Excel file has no duplicates but Access still reports a key violation?
Open the destination table in Access Datasheet View and compare its existing key values with the incoming rows. Also inspect every unique index in Design View > Indexes. A conflict may involve an existing record or a combination of fields rather than duplicates in one source column.
Can WPS Office fix the Access database itself?
No. WPS Spreadsheet can open the Excel source file so you can inspect and correct its data. Access primary keys, unique indexes, and import settings must be reviewed in Microsoft Access.




