key violations in append queries

[louise]

hello,
I need to import a table from another access db and append it to an empty, identically-structured table.


I need to do the same thing with a second table -- empty it in the destination db and import a table from the source db.
The two tables are related by a primary key in the source db and need to be have the same relationship in the destination db.

The problem I am having is "Key Violations" in the Append query -- i.e., duplicate primary key numbers between the source table and the emptied destination table.

Can you give me some suggestions for handling this? I need to do it with a macro or coding because the user, not me, will be doing the tasks.

Thanks!

[orange]

Tell us more about the tables in plain English.
Key violations indicate you have 2 or more records with the same key value. Primary keys in a relational table must be unique.

[louise]

Tell us more about the tables in plain English.
Key violations indicate you have 2 or more records with the same key value. Primary keys in a relational table must be unique.

Thanks for responding. Here is my situation, in “plain English”:

My project involves course planning in one "UpcomingSemester" database, and then registration/Administration in a second"Current Semester" database.

In the UPCOMING semester database, one table lists all thecourses ever offered.New courses areadded as the upcoming semester is planned (TblOverallCurric).Another related table has the course detailsfor the courses to be offered in the upcoming semester-- time, room, etc -- andthe fields for the upcoming semester and year (TblCourseDetails).Both tables have auto-numbered primary keys.These two tables are related through theprimary key of TblOverallCurric; they each have other relationships to othertables.

The CURRENT semester database has the same two tables,but the records reflect courses being offered this semester.The TblOverallCurric here is not up-to-datebecause it doesn't include any courses that are created for the next semester.The TblCourseDetails will be completelyout-of-date at soon as the current semester is finished.These two tables also have auto-numberedprimary keys.These two tables arerelated through the primary key of TblOverallCurric; they each have otherrelationships to other tables.

At the end of each semester, I have a macro to take thesesteps. (I developed this approach in order to preserve relationships betweenthese (and other) tables in each dataset.)
- In the Current semester db,delete all the records in of these two tables
- Import the two tables from theUpcoming Semester db
- Appendall the records from each Upcoming semester table to the parallel (empty) tablein the Current semester, to be ready for registration next semester
- Deletethe records in the TblCourseDetails tables in the Upcoming semester, to befilled when planning begins again.

As you can see, the Key Violation problem is in appendingthe Upcoming records in TblCourseDetails to the Current Semester db -- becausethe IDs are assigned in the Upcoming Semester and need to be maintained in theCurrent Semester where the ID number may have already been used. (No problem inTblOverallCurric because records/new courses are just added to the table.)And, of course, keeping the IDs of the tablesI import is critical because of the relationship between the two tables.

I am wondering now if it is bad/risky to just import theUpcoming TblCourseDetails, rename the old Current to TblCourseDetailsOLD, renamethe new one to TblCourseDetails, and delete the old one.(With AutoCorrect OFF??).

I also thought about a composite key of id, semester andyear to make them unique, BUT the semester/year values aren’t assigned untilthe course will definitely be offered. (And I am worried about all the changes this wouldrequire in the many queries, reports. etc in both datasets.)

Thanks for any help!I really appreciate it.

[orange]

I don't fully understand all the details, you mention 2 tables then indicate

they each have other relationships to other tables.

So the comments below may be missing things important to you.

If you delete all the records from the tables with autonumber PK, then do a compact and repair, the autonumbers for those tables will be removed/reset. Then when you import the Upcoming to Current, I think (untested) that you will generate new autonumbers.

You might try taking a copy of your databases, and after deleting the records from Current, do a compact and repair, then import Upcoming into the Current. And see if this accomplishes what you want.

Tables in relational database should have Primary Keys. You might, depending on details, be able to have all info in 1 database and use additional field(s) to make unique composite index or compound PK, then use a series of queries to work on the various sets of data.

Just some thoughts for consideration.
Good luck.

[John_G]

Just to add to the advice orange gave you -

Only rarely (if ever) can you import data from one autonumber-PK table into another (empty) autonumber_PK table, and expect the key to be retained, even if you do reset the sequence with compact and repair. A gap in the sequence in the source table will cause problems.

To make the import work, you can change the data type of the PK of the destination table from autonumber to Long Integer. The down side of that is that you then have to look after the PK numbering yourself, rather than using autonumber.

Another other thing you might try is to delete the relationships between the destination tables, do the import, then redefine the relationships. That would especially be true if you have referential integrity turned on.