VBA to identify records that exist in one DB/table but not in the other (no query please!).

[skydivetom]

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.

[ranman256]

That’s exactly what an outer join query is for.

[Gicu]

Tom, nothing really complicated in this but you need a few more pieces. You would probably need to create a table to hold the list of tables (easily populated by a loop of tabledefs collection) and the field name(s) that make up the unique identifier. One can have code that can extract the primary key of each table but in case that is missing it won't work.
Cheers,

[orange]

Tom,
Here's a routine that should handle all like-named tables.
Check it for concept and requirement. It can be modified relatively easily.

Sub ab()
    Dim dbthis As DAO.Database
    Dim dbThat As DAO.Database
    Dim tbl As DAO.TableDef
    Dim stablename As String
    Dim i As Integer
    Debug.Print "    Table                    2019         2022    Delta"
    Dim thatCount As Long
    Set dbThat = DBEngine.OpenDatabase("C:\users\jp\documents\2022j.accdb")
    Set dbthis = CurrentDb
    For Each tbl In CurrentDb.TableDefs
        If Not tbl.Name Like "Msys*" Then
        stablename = tbl.Name
        thatCount = dbThat.TableDefs(stablename).RecordCount
        
            Debug.Print tbl.Name & Space(30 - Len(tbl.Name)) & Format(tbl.RecordCount, "000") _
            & Space(10) & Format(thatCount, "000") & Space(5) _
            & Format(Abs(tbl.RecordCount - thatCount), "000")
        End If
    Next
End Sub

Result on the sample

[skydivetom]

Vlad -- thank you... I'm looking into this right now. Probably use a data dictionary (courtesy by Jack) to get me started on this.

//

Jack -- thanks for the feedback. My apologies for not having made the requirement a bit clearer. The JPG (w/ counts) was only meant to illustrate that there are differences. The "real" goal, however, is to actually product the real differences/values in another table.

Please see original post where I provided an example:
TABLENAME | VALUE
======================================
LK_ACTIVITY_UIC_CODE | 47622
LK_BLS_ACCIDENT_TYPE | 384
LK_BLS_BODY_PARTID | {NULL}
LK_BLS_BODY_PARTID | 18
...
...
...

[skydivetom]

Vlad:

Attached is the 3rd/new DB "Comparison". This one includes a form which calls the "Data Dictionary" function (code developed by Jack last year).

For now, I *imported* the 8 tables from file "2019". This was just to test the modification of the data dictionary (where I'm now excluding fields in table [Data_Dictionary] to be added to itself.

I believe the current table [Data_Dictionary] addressed the "few more pieces" you were referencing.

Naturally, once I deleted the eight (8) tables from file "Comparison 01", the data dictionary won't have anything to process, so nothing will be added.

So, the next questions are as follows:
1. Instead of creating this 3rd file, would it be easier/better to add the data dictionary to, e.g., file "2022" (or "2019")?
2. Either way, once the data dictionary is populated with all "LK_" tables and their fieldnames, what VBA would then actually compare the 8 tables (from 2022) in the DB against the 8 tables from 2019 (in the same subfolder)?
3. And finally, once the values have been identified, what function would append them to the new table "tbl_Deltas"?

Thanks,
Tom

[Gicu]

Hi Tom,

Finally had some time to download the files and have a look. Are all 100 or so tables single field tables with the field being the PK (not even that important if there is only one field)? If yes than the data dictionary is not really needed. I was thinking to a scenario where you could have multiple fields in the tables and composite primary key in which case to identify the unique records present in one file vs the other you would need to have multiple joins between the two tables.

Cheers,

[skydivetom]

Vlad:

Yes, all of the ~100 "LK_" tables contain only that single field where the fieldname = tablename MINUS the "LK_" prefix.

Although I'm not 100% certain that having a 3rd/new file would be the best approach, I believe it would make it easier IOT to *mitigate* the need to add a "suffix" (e.g., "_2019" OR "_2022" to each table).

Having the 3rd independent file though would allow me to simply drop both 2019 and 2022 files into the subfolder w/o any further manipulation. Then, 3rd file's VBA routine would execute the comparison and then have only a single table with the new values.

Hope this makes sense.

Thanks,
Tom

[Gicu]

Hi Tom,

Please review the attached file, should do what you want. You need to double click the two textboxes and select the "new" (2022) and "old"(2019) files. Also edited the data dictionary code to use the "new" file table list instead of the current db that won't have them.
Needs some more error trapping, validations, etc.

Cheers,

[skydivetom]

Vlad -- your solution is (again) BRILLIANT!!! It works like a charm.

Given it's in the middle of the night though (02 am in Virginia), I'll have a closer look tomorrow just to validate/cross-check the values in the real data set.

Just one question:
In a testing version, I moved the code into a separate module (vs. keeping in in the form). At this time, the following 2 lines error out:

    If IsNull(Me.NEW_FILE) Or IsNull(Me.OLD_FILE) Then MsgBox "File name(s) missing)": Exit Sub
    If Len(Dir(Me.NEW_FILE)) = 0 Or Len(Dir(Me.OLD_FILE)) = 0 Then MsgBox "One or both files do not exist": Exit Sub

I understand the reason is the "Me." reference... what should those 2 lines look like once I moved all 3 functions into the module?

[Gicu]

Hi Tom,
Glad to hear! Just replace Me with a full reference to the main form (Forms!F01_MainMenu).
Cheers,

[skydivetom]

Vlad -- everything works perfectly! This is yet another fantastic solution which automated this sub-process when comparing previous data pulls with most current data pulls.

Awesome... 1000 thanks!!

Cheers,
Tom

[orange]

Good work guys!
Vlad--very nice

[Gicu]

You're very welcome Tom!

Thanks Jack !

Cheers,