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.


7 Years
Discussion Span
Last Post by ryan311

Only partially works.

If I am trying to add for example: apple, grape, bannana and peach using INSERT with a SELECT DISTINCE.

The database already contain apple and grape.

The INSERT fails and bannana and peach are NOT added.


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.