VBA code for Access - compare tables

Question

I need vba code to compare two tables in an access database. The code will use the primary key "ID" on both tables and will pull data from Table1_field3 to populate table2_field5 in the second table.

Here are more details:

Table1

Id: primary key

field3: data to be "moved" to table 2 if there is a matching record in table 2.

Table2

Id:primary key

field5: field to be updated by data from table 1

The code will also create a table with the "unmatched" IDs between table1 and table2 (as an additional table)

Thank you.

Answer 1

Append or Update?  Append = add a new record, update = change the value of a field in an existing record.

This changes the value in existing records if Table2.Field has a value.

UPDATE Table2 INNER JOIN Table1

ON Table2.ID = Table1.ID

SET Table2.Field5 = Table1.Field3

WHERE Table2.Field5 is Not Null

This changes the value in existing records if Table2.Field hasNo value.

UPDATE Table2 INNER JOIN Table1

ON Table2.ID = Table1.ID

SET Table2.Field5 = Table1.Field3

WHERE Table2.Field5 is Not Null

or Table2.Field5 = ""  <<< add this if table2.field5 is a text field and is allowed to have zero length strings

---

John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County

Answer 2

to enhance this further.... how would I "append" field 3 to field 5...? In other words, if table2.field5 is not null, then append table1.field3 to table2.field5...

Answer 3

Thanks. This is great.

Answer 4

You should use queries to do this.  The queries are much more efficient at processing records than using VBA

UPDATE Table2 INNER JOIN Table1

ON Table2.ID = Table1.ID

SET Table2.Field5 = Table1.Field3

To get the unmatched records all in one query you would use a Union query

SELECT Table1.ID, "No Match in Table 2" as Unmatched

FROM Table1 LEFT JOIN Table2

ON Table1.ID = Table2.ID

WHERE Table2.ID is Null

UNION

SELECT Table2.ID, "No Match in Table 1" as Unmatched

FROM Table1 RIGHT JOIN Table2

ON Table1.ID = Table2.ID

WHERE Table1.ID is Null

---

John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County