I am trying to patch together some data that was entered into of copy of my database. I know I can link to it, but I want to consolidate all this into my home database so I can do a clean split later. I am more that halfway there, but I ran into something that puzzles me. There are three tables with autonumber primary keys. First I deleted all the relationships in the destination tables and the data, then did a Compact and Repair to reset the numbers. So far so good. I copied and pasted my data successfully. All the ID numbers remained the same. Again these tables are all related by these ID numbers. In the other database these tables are linked and have Referential Integrity enforced, including cascading deletes. Here are the three tables
tblTransactions (Primary Key - Transaction_ID, Foriegn Key - Submission_ID)
tblSubmissions (Primary Key - Submission_ID, Foriegn Key - Batch_ID)
tblBatchID (Primary Key - Batch_ID)
I think I've laid this out correctly.
First I relinked tblTransactions to tblSubmissions successfully using Referential Integrity. My last task is to link tblBatchID to tblSubmissions and Access tells me it can't create this relationship and enforce Referential Integrity.
I ran a query to see if there were any records in tblBatchID that had no related records tblSubmissions and there were none.
I'm stuck. Any ideas?
Paul