Troubleshoot Append Query Errors

Question

I am creating new tables using a select query and then an append query.  The select query to is to eliminate duplicate records before appending.  However when I run the append query I get a dialog box indicating that 9 of records cannot be appended due to errors. 

Is there any way to get access to tell me which records these 9 are?  I know I can figure it out, but it would be nice to see what records or what step of the process creates the error.

Answer 1

My real question was, iss there a way to see what records created a query error without the additional digging, regardless of the type of error that is occurring?

You'd  have to do it yourself by writing a query which returns the rows which will violate the key/index, but in the case of 'duplicates' this won't tell you which rows will be rejected, only which are duplicates, one of which will be appended.

Another way would be to append the rows one by one by iterating through a recordset and executing an SQL statement which INSERTs one row INTO the table for each row in the recordset.  You can trap the error by use of the dbFailOnError option of the Execute method and set up the error handler to tell you which row is being rejected and why.  In the case of duplicates, as one will be successfully appended, the error will be raised on the subsequent rows which duplicate a successfully appended row.  The successfully appended row from a set of duplicates  may not be the same as that appended by a single append query; it will depend on the what the recordset is ORDERed BY.

Answer 2

Thanks Ken,

But my basic question wasn't really about eliminating duplicates.  I was working on that and have resolved it. 

My real question was, iss there a way to see what records created a query error without the additional digging, regardless of the type of error that is occurring?  Access's dialog box just tells you that X number of records did not append etc. for Y reason(s) but not what records constitute X. 

I am guessing the answer is no, but I was hoping there might be a function feature I was unaware of.

Answer 3

The simplest way to eliminate duplicates is to let the Append query do it for you by uniquely indexing whatever combination of columns (fields) in the target table will have identical values in the case of 'duplicates'.  The easiest way to do this is to make the columns the composite primary key of the table, though if you are already using a 'surrogate' key, usually an autonumber, then you'd have to include the relevant columns in a separate unique index.  You should do this in any case to prevent further duplication in the future.

When you append rows from the source table only one instance of each 'duplicate' will be appended because of the key or index violations.  This is why you get the message in the dialogue.

To find out which rows are duplicated beforehand you can create a query using the duplicate query wizard, though it's a simple task to write it yourself.  Say you have a table Contacts and, somewhat unrealistically of course but it serves as an example, you want to identify those rows with identical FirstName and LastName values:

SELECT *

FROM Contacts AS C1

WHERE EXISTS

    (SELECT FirstName, LastName

     FROM Contacts AS C2

     WHERE C2.FirstName = C1.FirstName

     AND C2.LastName = C1.LastName

     GROUP BY FirstName, LastName

      HAVING COUNT(*) > 1);

Which row from each pair (or more) the duplicated rows will be appended is not identified of course.  If you want to specifically nominate which of the duplicates will be dropped then you can add a Boolean (Yes/No) column temporarily to the source table, DropMe say, and in the result table of the above query set the value of this to True for those of the duplicates you want dropped.  You then simply have to append the rows 'WHERE NOT DropMe' in the append query.

Answer 4

Sure; change the append query to a Select query and join it to the target table. You should see the nine duplicate records (if that's the error).

Note that running a select query first, and then running an independent Append query, doesn't do anything for you; the append query will append whatever records it includes. Access doesn't know or care about your having run the other query. So I'm not sure what you mean - could you perhaps post the SQL of the two queries?

 

The table I am appending to is blank.  The reason I was using 2 queries is I am trying to use the select query to filter out duplicate records in the source table.  The append query uses the results of the select query to post but it was still giving me errors. 

I think I have figured out at least part of the problem and will work on it.  I was just hoping there was an Access function that would allow you to see the errors or run a query in debug so you could see the error occur rather than have to figure it out everytime.  The error message box is not very helpful.

I ended up creating the table first and then elimininating the duplication afteward.

Answer 5

Sure; change the append query to a Select query and join it to the target table. You should see the nine duplicate records (if that's the error).

Note that running a select query first, and then running an independent Append query, doesn't do anything for you; the append query will append whatever records it includes. Access doesn't know or care about your having run the other query. So I'm not sure what you mean - could you perhaps post the SQL of the two queries?