Too little info offered.
Why does data disappear from your working file ? (delete records with delete related records, too ?)
How do you assign PK's in the tables ? (number, increment | number, max(PK) + 1 | UUID etc)
Is the solution hosted ?
How often do you have to do that ?
(because if the solution is not hosted and you only need to do it 1 a week, you can simply open your backup file and look at the data...)
Sorry for not providing enough information. The record(s) I will be moving into the active database are students that will need to be added to the active database (not just looked at).
The main file imports perfectly. The tables don't import at all. I know I just have something set wrong but don't know what.
you need to move to the correct source layouts with go to related record from your main (so that you get the correct found set to be imported) and to the correct destination layouts before importing. But that's just part of it: you don't want to recalc PK's and you don't want to create duplicate PKs.
The main question is: why do records disappear from the active database ?
We worked with a client with similar needs a while back. Rather than two separate files, why not put all records in a single file and add a field to indicate if the student is active or not? This allowed our client to keep a single file with all records up to date and making a student active (moving) only involved changing the status to active.
We already have a field for active and inactive and that works for students that come and go throughout the year. The students that I would like to import are ones that are from past years and are starting over. I would like to import them rather than manually entering them.
Nothing disappears from the active database. The related table information just doesn't copy over.
Ok, then it's just another layer if you will - you could mark them as "archived". Is there a reason to not have all the students in one database? Moving them back and forth, especially with related records (courses, history, payments, etc.), would only get more complicated. Plus, as spills points out, you don't want to recreate primary keys as that would cause other problems, including with the related records.