Using Append Queries with Linked Tables

[JDF_Horns]

I am using an MS 2007 database linked to DB2 tables. I am trying to
use an append query to add new records to a link DB2 table (with a
single field used a primary key). I was expecting that "new" records
would be added to my table, and that existing records would be
excluded (this is the way it works when I use append queries to add
records to tables that is not linked); however, I get an ODBC --
insert on a linked table 'table_name' failed. error.

The error message references SQL0803N One or more values in the
INSERT statement, UPDATE statement, or foreign key update caused by a
DELETE statement are not valid becauase the primary key, unique
constraint or index identified by "1" constrains table 'table_name'
from having duplicate rows for those columns. SQLSTATE = 23505.

Any suggestions for resolving this would be appreciated.

Thanks

JDF

 

[Dale Fye]

JDF,

1. What is the SQL string you are using to append records to the table?

2. Are you trying to write to the PK (I assume this is some sort of counter
or autonumber field)?

3. Have you checked to make sure there are not some UNIQUE indices on the
table you are trying to write to, other than the PK field?

 

[John Spencer]

You will need to write the append query so it excludes the records that
violate the integrity of the table.

One method would be to build a work table that parallels the structure of the
target and use a unique compound index (multi-field index) to populate this
work table. Delete all the records from the work table, append all the unique
records to the work table, then use the work table as the source to append
records into the target database.

Another method would be to generate a query that only has unique records.
Perhaps using DISTINCT or using an aggregate (group by or totals) query as the
source to eliminate the duplicates.

You might need to use an unmatched query to identify records in the target
that already exist.

Access works differently when it is working with its native engine (ACE) then
when working with an ODBC connected database. The database engine controls
what will happen in this situation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JDF_Horns]

You will need to write the append query so it excludes the records that
violate the integrity of the table.

One method would be to build a work table that parallels the structure ofthe
target and use a unique compound index (multi-field index) to populate this
work table.  Delete all the records from the work table, append all theunique
records to the work table, then use the work table as the source to append
records into the target database.

Another method would be to generate a query that only has unique records.
Perhaps using DISTINCT or using an aggregate (group by or totals) query as the
source to eliminate the duplicates.

You might need to use an unmatched query to identify records in the target
that already exist.

Access works differently when it is working with its native engine (ACE) then
when working with an ODBC connected database.  The database engine controls
what will happen in this situation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County







- Show quoted text -

Thanks - the "work" table resolved the issue.

JDF