Key violations on Append query - no indexes on table

[Carl Rapson]

I've searched through the forum for related posts, but I haven't seen one
with this particular situation. I'm attempting to append records from one
table to another. The source table is in an external database, linked
through ODBC. I created the Access table by copying the source table and
pasting the structure only, so I would assume the data types match. I then
removed all indexes from the Access table. When I run my append query, I get
a message stating 12 records (out of over 58,000) were not added due to key
violations.

As I said, I removed all indexes from the Access table. I also checked all
fields, and none have Yes under Required, and all text fields have Yes under
Allow Zero Length. So what else could give rise to this particular error
message? I tried running an unmatched query joining the 2 tables on the
primary key fields, but no records were returned. This makes me suspect that
another field in the record could be causing this. Is that a possibility?

Thanks,

Carl Rapson

 

[Jeanette Cunningham]

Hi Carl,
key violations usually refer to referential integrity type violations.
Check for a related table that is a parent table to your table and see if
the PK field from that table is missing from some of the records you are
trying to append.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Spencer]

One possibility, if you have a relationship established and enforced
between the imported table and another (parent) table then the foreign
key field in your imported table must contain values that are in the
field in the related table.

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

 

[Allen Browne]

Carl, here's a list of things to look for:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

 

[Carl Rapson]

Thanks, Jeanette. I checked, and no relationships are set up between tables
in this database.

Carl Rapson

 

[Carl Rapson]

Thanks, John. As I replied to Jeanette, I checked and there are no
relationships set up between tables in this database.

Carl Rapson

 

[Carl Rapson]

Thanks, Allen. I've checked the Access table and there are no indexes set up
on any field in the table, so I'm at a loss to figure out why I'm getting
the 'key violations' error. When I click Yes to allow the records to be
inserted, there is no 'Errors' table created showing which records were not
inserted, so I can't easily look at the records to spot something. I guess
I'll just keep working with it.

Carl Rapson

 

[Allen Browne]

Open the Immediate Window, and ask if there are any hidden indexes, e.g.
? CurrentDb.TableDefs("Table1").Indexes.Count

If there are, you can ask the Name of the index, the .Fields.Count, the name
of the first field as .Fields(0).Name, and so on.

 

[Carl Rapson]

Thanks Allen, that was it. It didn't occur to me that such indexes woulod be
carried over when I created the table, since the original table isn't from
an Access database. I'll remember to check the Indexes collection next time
I have such issues.

Carl Rapson