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

Posted on 2008-11-07
Last Modified: 2013-11-29
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?
Question by:jfikes
    LVL 74

    Accepted Solution


    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.


    Author Comment

    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now