Append Query key violations... Autonumber entries

A

awkusmc

You all have talked about this ... I haven't seen an answer quite matching my
problem.

I have an empty MS Access 2000 DB (structure only) and I am looking to
populate it with old records into the new structure. So it is a conversion.
Autonumber is the key id field(not used in coversion or selection) and I
don't care what MS Access builds for the autonumbered key... and there is no
reference other than it is a place holder to keep all of the information
seperate (no dups).

Heres the SQL that MS Access built;
INSERT INTO tblAAN ( LastName, FirstName, MI, DateReportRecieved, [Date
Report Entered] )
SELECT tblAANold.LastName, tblAANold.FirstName, tblAANold.MI, tblAANold.
DateReportRecieved, tblAANold.[Date Report Entered]
FROM tblAANold;

Here is MS Access's help information:
I'm getting a key violation message.
A key violation occurs if:

You attempt to run a query that appends, deletes, or updates records in a way
that violates the rules of referential integrity for related tables.


Your query attempts to append or update records that contain primary key
values that already exist in the destination table.
If you run such a query, it will not modify the records that cause the key
violation.

To modify records in a way that will violate referential integrity, you can
break the relationship between the affected tables or turn referential
integrity off, and then modify the affected tables one at a time. However,
the resulting data in the two tables may then conflict.
 
K

kingston via AccessMonster.com

First of all, what are the keys in the table? If the problem is with the
autonumber field, clear the table and do a compact and repair on the database.
Perhaps in testing, the autonumber got stuck somehow. If the problem is with
referential integrity then you have no choice but to clear the relationships
and rebuild them once all of the data is imported.
You all have talked about this ... I haven't seen an answer quite matching my
problem.

I have an empty MS Access 2000 DB (structure only) and I am looking to
populate it with old records into the new structure. So it is a conversion.
Autonumber is the key id field(not used in coversion or selection) and I
don't care what MS Access builds for the autonumbered key... and there is no
reference other than it is a place holder to keep all of the information
seperate (no dups).

Heres the SQL that MS Access built;
INSERT INTO tblAAN ( LastName, FirstName, MI, DateReportRecieved, [Date
Report Entered] )
SELECT tblAANold.LastName, tblAANold.FirstName, tblAANold.MI, tblAANold.
DateReportRecieved, tblAANold.[Date Report Entered]
FROM tblAANold;

Here is MS Access's help information:
I'm getting a key violation message.
A key violation occurs if:

You attempt to run a query that appends, deletes, or updates records in a way
that violates the rules of referential integrity for related tables.

Your query attempts to append or update records that contain primary key
values that already exist in the destination table.
If you run such a query, it will not modify the records that cause the key
violation.

To modify records in a way that will violate referential integrity, you can
break the relationship between the affected tables or turn referential
integrity off, and then modify the affected tables one at a time. However,
the resulting data in the two tables may then conflict.

---
Any suggestions? Like breaking the referential integrity for the conversion
and putting it back in?

Thanks in advance...
 
G

Guest

#1. After making sure that the empty database's tables are really empty, do a
compact and repair. That will allow insertion of records with autonumbers.

#2. Make sure that you are importing the parent tables first. If you have
referiential integrity enabled between two tables, you won't be able to
import the child table until after the parent table is populated.

#3. If you are inserting data into a stand-alone table (so it seems) and the
autonumber isn't used in any relationships, make sure that field is not part
of the insert statement.

#4. Make double, no triple, sure that your old table doesn't have duplicate
data or nulls in the primary key field(s) or unique indexed fields of the new
table.

#5. If it's still giving you trouble, possibly the new database is alread
corrupt. Compact and repair should fix that though.
 
A

awkusmc

After talking to a co-worker and reading what you all wrote.

I think I've got it fixed. Thanks for the help!
 
A

awkusmc

awkusmc said:
After talking to a co-worker and reading what you all wrote.

I think I've got it fixed. Thanks for the help!

P.S. I broke the relationships... it was single table. Long story short:
Removing old data fields, adding new ones, trimming that wonderful 50
character default space to what it should be. And put them (relationships)
back.

The complex is easy... the easy is sometimes the hardest.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top