Append Query - Key violation

[Gwynn]

I have a dummy database with numerous tables. For one of the tables,
tblDepartments (pks HospID and CostCtr), I want to import data from an Excel
spreadsheet. This data includes CostCtr, Department, DeptManager, DeptExt.
The import (DataInfotbl) works just fine.

When I try to append DataInfotbl to tblDepartments, I receive the following
error message:

set 0 field(s) to Null due to a type conversion failure, and it didn't add
89 record(s) to the table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule violations.

In playing around in an attempt to figure out the issue, I copied
tblDepartments to Playtbl. I removed the primary keys and changed the
properties for Required to No, Allow Zero Length to Yes and Indexed to Yes
(Duplicates Allowed). The append from DataInfotbl to Playtbl worked fine.

Going back to my original table, tblDepartments, primary keys are set and
properties are set to: Required - Yes, Allow Zero Length - No, and Indexed -
No. These settings are correct in a "live" working copy of the same database
and the application functions correctly. Note: HospID does and should have
duplicate data.

Since the Playtbl appended with no errors, I assumed I could just take that
data and append to tblDepartments . No such luck! I receive the violation
error.

Any suggestions?

 

[louisjohnphillips]

I have a dummy database with numerous tables.  For one of the tables,
tblDepartments (pks HospID and CostCtr), I want to import data from an Excel
spreadsheet.  This data includes CostCtr, Department, DeptManager, DeptExt.  
The import (DataInfotbl) works just fine.

When I try to append DataInfotbl to tblDepartments, I receive the following
error message:

set 0 field(s) to Null due to a type conversion failure, and it didn't add
89 record(s) to the table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule violations.

In playing around in an attempt to figure out the issue, I copied
tblDepartments to Playtbl.  I removed the primary keys and changed the
properties for Required to No, Allow Zero Length to Yes and Indexed to Yes
(Duplicates Allowed).  The append from DataInfotbl to Playtbl worked fine.

Going back to my original table, tblDepartments, primary keys are set and
properties are set to: Required - Yes, Allow Zero Length - No, and Indexed -
No.  These settings are correct in a "live" working copy of the same database
and the application functions correctly.  Note: HospID does and should have
duplicate data.

Since the Playtbl appended with no errors, I assumed I could just take that
data and append to tblDepartments .  No such luck!  I receive the violation
error.

Any suggestions?

Is it possible that tblDepartments has primary key with an auto
increment property? And that the DataInfotbl table is trying to
insert rows into tblDepartments while specifying a key value?

If the values passed from DataInfotbl conflict with those existing in
tblDepartments, the key violation will occur.

If the situation is as described, consider inserting DataInfotbl
without specifying the column that is the primary key of
tblDepartments--thus letting it auto-increment.