Show Records that were unable to be imported

[dmijato1]

Is there anyway to export the rows that are unable to be appended to a table due to the "Microsoft Access was unable to append all the data" error? For example, if I have an Excel file has 200 rows of which 10 are already in a table in access and I try to import the 200 rows I will get the error: Microsoft Access was unable to append all the data, which is expected if it violates the unique key rule. What I need is to see which 10 were unable to be appended. In essence, when I get the error I would like the rows that are unable to be imported to be exported to an Excel file so I can see which rows are causing the error so I can QA/QC.

 

[Joe4]

Welcome to the Board!

Since those 10 records are unable to be reported, you will be unable to see them (where would they come from)?

One option is to import the records to a temporary table, and then you can do a query between the temporary table and the final table to show you all matching records (the 10 that will not be able to be imported). Then you can use an Append Query to write the rest from the temporary table to the final table.

 

[Micron]

If this will be a frequent occurrence, you can execute the query in vba and create a recordset with it. Loop through the recordset and append each record. If it fails, write the record somewhere else. Not as easy as Joe4 suggests. You could also copy the target table, remove the index(es) in the copy that do not allow duplicates, and append your data to it. Then sort it on the index fields and look for the offenders (duplicates).