Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: automatically add records to linked table

    I'm new to Access and I just need to determine whether this can be done.

    I have one imported DBF table1 with basic customer info from an ERP system. I've linked it to a second table2 in Access that I populated with records by copying and pasting table1's ID column. The two tables are linked and I have a simple form with fields from both tables. I'd like to be able to maintain table2 with new user data but update table1 with a fresh import that might contain new records from the ERP. I want the new records to show on the form and automatically add to table2 so that the user can maintain table2 data for them. I've played with integrity enforcement and cascading fields but no luck. Can this be done in Access?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Import a fresh copy of the external data into a temporary table (TableTemp).

    2. For updating data in Table1 you need to:
    a) Update rows in Table1 that also exist in TableTemp:
    Code:
    UPDATE Table1 INNER JOIN TableTemp ON Table1.ID = TableTemp.ID 
    SET Table1.<Column1> = TableTemp.<Column1>, ... Table1.ColumnN = TableTemp.ColumnN;
    b) Insert new rows from TableTemp into Table1:
    Code:
    INSERT INTO Table1 ( ID, <Column1>,..., <ColumnN> )
    SELECT TableTemp.ID, TableTemp.<Column1>,..., Table1.<ColumnN>
    FROM Table1 RIGHT JOIN TableTemp ON Table1.ID = TableTemp.ID
    WHERE Table1.ID Is Null;
    c) Insert new IDs into Table2:
    Code:
    INSERT INTO Table2 ( ID )
    SELECT Table1.ID
    FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
    WHERE Table2.ID Is Null;
    d) You'll possibly want to delete rows from Table1 and Table2 that does not exist anymore in TableTemp.
    i. For Table1:
    Code:
    DELETE Table1.* FROM Table1
    WHERE Table1.ID NOT IN ( SELECT TableTemp.ID FROM TableTemp );
    ii. For Table2:
    Code:
    DELETE Table2.* FROM Table2
    WHERE Table2.ID NOT IN ( SELECT Table1.ID FROM Table1 );
    In this last case you can alternatively enforce referential integrity between Table1 and Table2 for CASCADE DELETE.

    3. Delete all rows from TableTemp:
    Code:
    DELETE FROM TableTemp;
    or remove TableTemp from the database if it's recrated by the import process:
    Code:
    DROP TABLE TableTemp;
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    2c works

    Thanks, step 2c was all I needed. I want to completely ovewrite table1 because some fields in old records may have changed. And I don't need to delete any records in table2 because records in table1 are almost never deleted.

    ANother poster on another forum pointed out a problem overwriting a linked table1 but I just deleted the link first, deleted table1, imported the new DBF, ran your insert into query, and remade the link. Seems to be okay on quick inspection but if you see a problem with this let me know.

    Not as seamless as I hoped but not bad. Thanks again.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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