Add Records from a Recordset to another Recordser if not exist (1 Viewer)

[jsdba]

Table1
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Tomato|
Veg | Lettuce|
Grain | Bread |
Dairy | Milk |

Table2
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Lettuce|
Grain | Bread |

How do i add records from Table1 to Table2 if the record does not already exist in table 2 using VBA

 

[sneuberg]

Usually this is done with a unmatched query that is converted into an append query. If you unload a database with these tables with some data I'll give you a concrete example. I will need to know which fields are the primary keys.

 

[static]

SQL = "INSERT INTO Table2 ( food, item ) "
SQL = SQL & "SELECT Table1.food, Table1.item "
SQL = SQL & "FROM Table1 LEFT JOIN Table2 ON Table1.food = Table2.food AND Table1.item = Table2.item "
SQL = SQL & "WHERE Table2.food Is Null"
CurrentDb.Execute SQL

 

[JHB]

Table1
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Tomato|
Veg | Lettuce|
Grain | Bread |
Dairy | Milk |

Table2
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Lettuce|
Grain | Bread |

How do i add records from Table1 to Table2 if the record does not already exist in table 2 using VBA

Why do you want two table with the same data, it is poor database concept?

 

[Solo712]

Why do you want two table with the same data, it is poor database concept?

Right. You can accomplish that with one table with Food, Item adding a column called Group. Then test if food+item you are testing exist in Group 2, and the combo does not exist you add it.

If DCount("*", "Foodtable", "Food = '" & myfood & '" AND Item = '" & mytitem & "' AND Group = 2") = 0 Then 
   CurrentDB.Execute "INSERT INTO Foodtable (Food, Item, Group) VALUES ('myfood', 'myitem', 2)"
End if

Best,
Jiri

 

[JHB]

Right. You can accomplish that with one table with Food, Item adding a column called Group. Then test if food+item you are testing exist in Group 2, and the combo does not exist you add it.

But still two tables with the same data, why?

 

[jsdba]

But still two tables with the same data, why?

JHB The business logic is very complex. The actual table structure is nothing like what i posted. I just wanted to see a how i can accomplish this using vba/sql.

 

[jsdba]

SQL = "INSERT INTO Table2 ( food, item ) "
SQL = SQL & "SELECT Table1.food, Table1.item "
SQL = SQL & "FROM Table1 LEFT JOIN Table2 ON Table1.food = Table2.food AND Table1.item = Table2.item "
SQL = SQL & "WHERE Table2.food Is Null"
CurrentDb.Execute SQL

Worked thanks