What is the best way to import external data into a linking table that has multiple one-to-many relationships?

I understand that there is no way to import text or spreadsheet data to a linking table that has multiple one-to-many relationships (see the diagram of the relationships on the attachment--note the CompanyContacts table). So, if using the external data import function in Access 2007 will not work, what other method(s) could be used to import external data into the CompanyContacts table? The data that I need to import to CompanyContacts causes key violation errors with both the Companies and the Contacts tables. Both Companies and Contacts uses a single primary key (each) that is set to Autonumber, duplicates are not allowed. When I tried to import the data to the CompanyContacts table, I got the expected key violation error message. I have too much data for manual entry, so how could I import external data to the CompanyContacts table?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:

You can't add records to the many side of a relationship before you add the corespondig "One" record.
(For examplel: You cannot import Orders for customers who do not exits yet.)
So you must import the One records (New Customers) first.

So it can be done, but you just have to do this in the correct order.

It also might be difficult if you relatinships are complex.

What is typically done is to import the data to "temporary Staging" tables, which will be stand alone tables.

When all the data is consistient, you can append the records to the linked table.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfikesAuthor Commented:
This worked. I made sure that the two primary keys in the linking table were set to allow duplicates. I used an append query to move the data from the external files to temporary tables. When I ran the append queries, no error messages occurred -- and everything works perfectly. Thanks for the excellent advice.
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.