Update access table with values ​​from another table using VBA

Question

I have two tables, table 1 and table 2, in the database. I am trying to update table 1 using VBA code based on the data in table 2.

Example:

Table 1

PartNo  Price  Description
--------------------------
A      100    
B      200      Bad
C      300

table 2

PartNo  Price  Description
--------------------------
A        700
B        200      Good
D        900      Used

After updating, table1 should be updated with those prices or descriptions that have been changed, where table1 PartNo = table 2 PartNo and add any new elements that were present in table 2.

Table 1

PartNo  Price  Description
--------------------------
A      700
B      200      Good
C      300
D      900      Used

I can’t understand what is right, appreciate the help.

Answer 1

You can do this with two statements, update and insert, for example:

Update:

UPDATE Table1 
INNER JOIN table2 
 ON(table1.partNo = table2.PartNo)
SET table1.price = table2.price,
    table1.description = table2.description

And then insert:

INSERT INTO table1 (PartNo,Price,Description)
SELECT PartNo,Price,Description FROM table2 t
WHERE NOT EXISTS(SELECT 1 FROM table1 s
                 WHERE t.PartNo = s.PartNo)