Table not updating propperly after changing records through ADO

[RCurtin]

OK I've been going round in circles tyring to solve this problem. I have imported data into a temp table and parsed the records through ADO. That works perfectly. When I open the temp table all the records are there with the necessary changes.

However the next step is to append this data to another table. Now if I run this query in Access as normal it works fine and sometimes when I run the code it works. But sometimes it does not append the data - the runSQL command seems to be working from the data before it was updated through ADO. How can I get it to append the updated data??

parseNEDrawingNums  'parses records in temp table
DoCmd.RunSQL "INSERT INTO tblVendorNEDrawings (MHINum, VNumArea, VNumType )SELECT MHINum, VNumArea, VNumType FROM tempNEVendor"

 

[RuralGuy]

Use CurrentDb().Execute MySQL, dbFailOnError and see what error you are getting.

 

[RCurtin]

I tried that and this is the error that I got:

Error number: 3022
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.


I am appending the data from tempNEVendor into tblVendorNEDrawings after it has been parsed. If I do this in 2 seperate steps i.e.
-parse the data in tempNEVendor
-append it to tblVendorNEDrawings

It works fine and just doesn't add the 13 dublicate records. But what is happening her is that it even though the data is being parsed, the runSql command doesn't seem to be working from the updated parsed data.

Any idea what could be happening?

 

[RCurtin]

Sample DB

I have just attached a scaled down version of the database if someone could have a look at it..