MS Access append to SQL key violations when key is unique

Question

I am appending records from an MS Access table into a table in SQL Server 2012 through linked ODBC in MS Access. The data is structured the same in the SQL table as the MS Access table, including the keyfield which is SurveyID. I keep getting an error message saying it can't append 1 record (of 1493) because of key violations. Even though it is the keyfield in the Access table, I have tested using a groupby query to ensure there are no dups in the table I am trying to append. I have also written innerjoin queries to make sure that no SurveyID in the MS Access table is already in the SQL table. I also compacted the MSAccess db, in case there was a problem with that table. Any other suggestions for finding out what is going on here?

Answer 1

So I gave my database to someone who wasn't as focused on the key thing as me. Turned out that the record wouldn't append because of a validation constraint on one of the non-key fields, and MS Access didn't report the error type correctly. Bummer that happens, but good to know.