Wierd Append error

[martinmike2]

Hello, I am getting a wierd append query error.

When I go to run an append query to append new records to my working
table from an updated table that I get from higher up the chain I get
the strangets error. This particular table has 43 new records and no
primary key. My working table has no Primary key as well. Now, what
I am getting is the "Can't append 43 records due to key violations".
Now........I have no key!!!1


Any Ideas? This is something I should be able to solve on my own, but
my brain isn't working this morning....maybe I need some coffee, lol.

 

[John Spencer]

Key violations can occur if you have a related table and have enforced the
relationship. Usually you would be inserting a value into the foreign key
field that does not exist in the related table primary key.

You might also check to make sure you have no field indexed with no duplicates
allowed. I don't think that will give you the Key violations message, but it
might.

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

 

[martinmike2]

John,

That makes sense. The table I am trying to append to is on the Many
side of the relationship and I have RI set. Is there a sperate
procedure required to append to a table with RI?

 

[John Spencer]

No, there is not separate procedure. You have to make sure that the value
exists in the one-side table or you have to drop relational integrity. Not a
good idea to drop relational integrity.

You can identify the problem records using the unmatched query wizard and then
decide what to do. Add a new record(s) to the "parent" table, delete the
"bad" records before importing, click on the option to import anyway and
accept that the records with "bad" values won't be imported.

The SQL to identify invalid foreign key values would be something like:
SELECT DISTINCT ImportTable.ForeignKey
FROM ImportTable LEFT JOIN ParentTable
ON ImportTable.ForeignKey = ParentTable.PrimaryKey
WHERE ParentTable.PrimaryKey is Null


The SQL to add just the primary key would be something like the following.
This is probably not a good idea, but it is possible to do this.
INSERT INTO ParentTable (PrimaryKey)
SELECT DISTINCT ImportTable.ForeignKey
FROM ImportTable LEFT JOIN ParentTable
ON ImportTable.ForeignKey = ParentTable.PrimaryKey
WHERE ParentTable.PrimaryKey is Null


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

 

[Tom van Stiphout]

On Fri, 12 Sep 2008 06:29:42 -0700 (PDT), martinmike2

Yes (in a way): you can only insert records that do not violate RI.
This is a VERY GOOD thing. THANKFULLY you had RI in place and were
stopped from adding logically inconsistent data.

-Tom.
Microsoft Access MVP

 

[martinmike2]

ok, heres the scoop.

In the table that needs the append there are 4 fields (auic, bin, bsc,
wccode), in the table that I am appending from there are 3 fields
(auic, bin, bsc). The relationship is on the WCCODE field of the
child table and the WCCODE field of a seperate table that contains all
of our work centers and thier details. On appending to the child
table the WCCODE field would be left blank to be filled in at a later
date to "assign" the record to a work center. As "assigning" these
records before the appending would be impractical, requires several
meetings and finagling at the management level to accomplish this, I
need the records to have the WCCODE field blank on append. Could this
be accomplished by defaulting the field to say "1000" and then setting
a work center called "Not Assigned" as 1000?

 

[martinmike2]

ok, heres the scoop.

In the table that needs the append there are 4 fields (auic, bin, bsc,
wccode),  in the table that I am appending from there are 3 fields
(auic, bin, bsc).  The relationship is on the WCCODE field of the
child table and the WCCODE field of a seperate table that contains all
of our work centers and thier details.  On appending to the child
table the WCCODE field would be left blank to be filled in at a later
date to "assign" the record to a work center.  As "assigning" these
records before the appending would be impractical, requires several
meetings and finagling at the management level to accomplish this, I
need the records to have the WCCODE field blank on append.  Could this
be accomplished by defaulting the field to say "1000" and then setting
a work center called "Not Assigned" as 1000?

ok, I just tried this method and it worked as advertised. Now i can
just run a query to find the unassigned records, and build a form to
assign them to a w/c.

Appreciate the help!