Experts:
I need some assistance with (preferably) developing a VBA routine which compares tables (identical in structure) and outputs any new values that exist in file "2022" but don't exist in file "2019".
Quick background:
- Attached are 2 DBs ("2019" and "2022").
- For illustration purposes only, both DBs contain only 8 tables. In my actual DB, however, I have nearly 100 tables each.
- Now, with respect to records, table [LK_ACTIVITY_UIC_CODE] (in file "2019") contains 22 records.
- Alternatively, table [LK_ACTIVITY_UIC_CODE] (in file "2022") contains 23 records.
- So, I need to identify this one (1) new record that exist in the "2022" file... well, all new records across the 8 tables.
Here's what I do NOT want to do:
1. I do NOT want to add, e.g., the eight (8) tables from "2022" and add them to "2019" (and adding some form of table suffix such as "_2022").
2. Based on 1., I do NOT want create 8 LEFT-JOIN queries to identify the one record that exist in "2022" but NOT in "2019".
Instead, I would like to achieve the following via VBA:
- For instance, place both files ("2019" and "2022") into subfolder "Temp" (on my desktop).
- Create a new (third) DB (e.g., "IdentifyDeltas").
- This new DB then contains a form which would call/execute some VBA code.
- The VBA code would then parse/scan though all 8 tables (in both files).
- For each table set where a new record (or number of records) was/were found, it then appends the identified value (record) into a new table "tbl_Delta".
For example, this new table "tbl_Delta" would contain both "table reference" as well as each "value" that exist in the "2022" table (but not in "2019").
TABLENAME | VALUE
======================================
LK_ACTIVITY_UIC_CODE | 47622
LK_BLS_ACCIDENT_TYPE | 384
LK_BLS_BODY_PARTID | {NULL}
LK_BLS_BODY_PARTID | 18
...
...
...
Does anyone know of a VBA function which would add/append all new 2022 records into the new table "tbl_Delta"?
Thank you,
EEH
P.S. For testing purposes only, I created queries to identify a few tables which had some differences and some that didn't have any changes. The JPG summarizes the net delta for these 8 example tables. I can't imagine doing this for all 100 tables though.