Key violation error - while importing excel to table

Question

I use to import xlsx file into Access table which was working fine by the help of the below code 

Below Final portion of Code

DoCmd.TransferSpreadsheet acImport, 8, "KomtraxImport", strExcelPath, False, "A2:P45"

It works with no error on the Laptop having Windows 7 And Office 2013

But when the same accdb file is used on another laptop having Windows 10 and Office 2013, its producing error as Key violation

But cross checked , no field empty and no records lost. It worked but with error. Its making me worry for later.

Went through web, found some people has already experienced but no clear answer, maximum advisor focus on the table validation rules and field types. I checked all nothing found.

But my question is that  if there is any issue of the key violation, then why it is working on one laptop having windows 7 and why not working on windows 10. It should work on both or its should have error on both.

Please advice if any solution can be done. 

Answer 1

The answer is in the first line of the error message: 1 record did not make it due to key violations.

What does that mean?

It means that you have a key = index, and it has to be a unique index (e.g. the primary key; but there could be another one). This index enforces uniqueness in the field. You then try to add another row with the same value in that field, and the index will not allow it, and your error message is generated.

This time it has nothing to do with data type or field size.

Answer 2

I was counting on the relevance of the screenshot. It indicates that the contents of field(s) in one record were deleted, and that 0 records were lost. I know the OP reported that the error was "...key violation...", but I placed greater trust in the screenshot being accurate.

Answer 3

We can't see the data in the import. We can't see the existing data in the two tables. We can't see the field types in the fields in those tables. We can only base a suggestion on the available information, which is that the data being imported doesn't conform to an expected datatype or fieldsize in the table in Access. 

So, please confirm HOW you used the same accdb on the Windows 10 computer.

Did you copy the accdb from the Win 7 computer to the Win 10? Did you ensure all of the existing records in the table(s) are identical? Did you import the same set of data from the same Excel file?

Also, inspect the imported data in the table AFTER the import on the Win 10 computer. According to the error message, one of the fields in one of the records in that table will have no value in it even though it should have a value and all other records will have a value in that same field. This can be a bit tedious, but it is a crucial trouble-shooting step you need to perform. One way to identify it might just be sorting the table on each field in turn, looking for fields where there is an empty (Null or ZLS) value that sorts to the top. Check those out for possible culprits.

Answer 4

Was able to track the issue - the excel file's one of the row was creating that issue.

As excel file is been created by another Equipment-Monitoring System website.

On windows 7, i was testing with date 7-Aug data excel file.

But when i was testing to anther PC on windows 10 was having today date file (and in this data type of one row was disturbed,).

So was unable to understand and was thinking that both the file is same and starting trouble shooting the system instead of checking the excel file 1942 rows.

After getting your advice, started the checks and tracked it, Fix the data type in the excel file and import happened without any error dialog.

Thanking you and Mr. Tom for your advise.

Best regards.