I am having a problem with the SQL INSERT command in VB. I am trying in move data from one table to another. The problem is I am going from a table with duplicates to one with no duplicates. Since there are duplicates the SQL statement bombs. Here is the SQL code:
INSERT INTO tblAssignmentAttendWard ( fldAssignAttendingWard ) SELECT tblPatients.fldAttending FROM tblPatients)
The: tblPatients.fldAttending contains duplicate entries
The: tblAssignmentAttendWard.fldAssingAttendingWard (is a Key with NO DUPLICATES)
Here is the error:
Error Number: -2147217900
[Microsoft][ODBC Microsoft Access Driver] 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.
The frustrating problem is that if I copy the SQL statement into a query in Access (that is the database I am using) it works fine.