Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004

    Unanswered: insert data into more than one table

    Hello Everybody,

    I am using Microsoft Access 2003 under Windows 2000.
    Briefly described what I want to do is insert data out of an Excel File into my Database (which constits of serveral tables with several relationships).

    What I have done so far is: I have linked the Excel File into my DB.

    What I want to do now is inserting the data out of the linked table into the therefore provided tables.

    Can anyone help me with that? Maybe provide me with some tutorials or links to related topics or best of all give me some advice with the query??
    So far I have tried an insert statement, but I don't know how to use it for several tables...

    Thank you very much in advance.

    Kind Regards,

  2. #2
    Join Date
    Mar 2004

    Re: insert data into more than one table

    To run inserts over several tables you will need to run multiple Inserts. i.e.

    Connection.Execute "INSERT TABLE1(field1) Select Field1 FROM ExcelTable"

    Connection.Execute "INSERT TABLE2(field1) Select Field1 FROM ExcelTable"

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Apr 2004
    Thanks for your quick answer.
    I think I will need a little more help on that.

    Do I have to create a new Access Module to write all the insert statments ?
    How?( What do I have to write in the Module to get access to my tables) ?


  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    it's not easy to do, and it's impossible to tell you how without details of your tables, relationships, and the file to import. if you provide those details, they will probably be too detailed so nobody here will want to help you.

    example (since i'm bored at the moment): imagine a two table jobbie that holds car manufacturers and the models they make.

    tblManuf (manufID-autonumber-PK, manufName-text)
    1, Volkswagen
    2, Fiat
    3, Mercedes
    4, Ford

    tblModels (modelID-autonumber-PK, manufID-FK, modelName-text)
    1, 1, "Golf"
    2, 1, "Polo"
    3, 1, "Passat"
    4, 1, "Turan"
    5, 4, "Scorpio"
    6, 4, "Maverick"

    now you propose to bring in Excel data along the lines of:
    "Fiat", "Panda"
    "Ford", "Escort"
    "Mercedes", "E320T"
    "Mercedes", "S600"
    "Chrysler", "Voyager" for each line in your XLS you need to:
    -get manufID from tblManuf to match "Fiat" (insert new manufacturer record in tblManuf if necessary)
    -check if modelName for manufID exists in tblModels and insert if necessary.

    in brief, you will need to do a full search of each related table to find the key field that matches your wannabee imported record. this is a nasty job and it is not made easier by XL allowing free text entry -- you might need a plan to handle "MB", "Murcedes", "Mycedes", "MyCeeDees" etc

    this is a good time to tell your boss you will need 3 months help from a student during the summer.

    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Thanks for your answer. It helped me a lot.
    Maybe could you provide me some tutorial stuff or something that will help me on that?

    By the way... I am the student ... :-(


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts