Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Unanswered: CASCADE INSERT via ADO in VBA

    I have an access database that has one parent table with a idnumber field, that is linked both 1-to-1 and 1-to-many to about 10 other tables in the database.
    I have VBA code that INSERTS new records into main table, using ADO. I need the code to automatically create a record in each of the other tables that have the idnumber field automatically set to the same idnumber is the new record in the main table, and every other field empty. Is there a way to do this, without hardcoding a loop into my VBA. (note: my VBA code is in excel, but I doubt it makes a difference).

    I have tried several ideas including setting different relationships in Access, and using different commands in my SQL commands, but nothing seems to work.

    Any suggestions?

    Thanks,
    Viggy

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: CASCADE INSERT via ADO in VBA

    Originally posted by Viggy
    I have an access database that has one parent table with a idnumber field, that is linked both 1-to-1 and 1-to-many to about 10 other tables in the database.
    I have VBA code that INSERTS new records into main table, using ADO. I need the code to automatically create a record in each of the other tables that have the idnumber field automatically set to the same idnumber is the new record in the main table, and every other field empty. Is there a way to do this, without hardcoding a loop into my VBA. (note: my VBA code is in excel, but I doubt it makes a difference).

    I have tried several ideas including setting different relationships in Access, and using different commands in my SQL commands, but nothing seems to work.

    Any suggestions?

    Thanks,
    Viggy
    Let me make sure I understand you. You are already adding a new ID number to the main table using VBA and you want to add this number to any table linked to main table via the ID?

    Interesting. Why don't you want to hard code? You are already doing that with the main table.

    If you set up a function or procedure and passed it the names of the tables that needed to have a value appended and the value you wanted to append, you would be allowing for future changes to the table names and/or additional tables. I'm not really all that familiar with what is available in Excel but if you could use something like a combo box or listbox that captured the names of the tables automatically, you could either add to them all at once or set up code to select each one individually such as in a multiselect listbox.

    Just first thoughts.

    Hope is sparks some ideas.

    Gregg

  3. #3
    Join Date
    Jan 2004
    Posts
    18
    A user is able to add information to a worksheet related to the main table. IF a save button is pressed, the code checks for a company ID number, and then it automatically inserts this information into a new record, and automatically assigns it a new ID number if no ID number previously existed.
    Related to this main table are a descent amount of tables, each linked to the main one via ID number. What I would like to do (if possible), is if I create a new record in the main/(parent) table, then a correspodning record is generated in each linked/(child) table.

    I searched around, and this doesn't seem possible within ADO in VB, but I'm holding out hope that there's some idea i've been missing.

    Thanks,
    Viggy

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by Viggy
    A user is able to add information to a worksheet related to the main table. IF a save button is pressed, the code checks for a company ID number, and then it automatically inserts this information into a new record, and automatically assigns it a new ID number if no ID number previously existed.
    Related to this main table are a descent amount of tables, each linked to the main one via ID number. What I would like to do (if possible), is if I create a new record in the main/(parent) table, then a correspodning record is generated in each linked/(child) table.

    I searched around, and this doesn't seem possible within ADO in VB, but I'm holding out hope that there's some idea i've been missing.

    Thanks,
    Viggy
    If you are looking for something automatic you may be right. I haven't heard of it if there is. Most of my updating occurs during input into Form/Subform structures with the linking fields. Other that that, I usually use hard/flexible code where I can.

    Good luck.

    Gregg

Posting Permissions

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