Compare Two Tables and Find Changes VBA

**zmbd** · May 16 '17, 06:07 AM

apopa,
For a small number of fields in the tables, you should be able to do this with pure SQL; however, if you have fields with null values (no data) then the comparison is a bit tricky so before we tackle the pure SQL version we would need a bit more detail on your data.

As for the VBA
Follows is a really bodged togeither method of one way to loop through your record.... mind you, this a 1am my time and I'm on my way to bed so I don't guarantee the quality ((Z-Z))

HOWEVER, I wanted you to compare the way my code is written vs what you have... (I've omitted my error check code here, more on that later perhaps)

Code:

Option Compare Database
Option Explicit

Sub logdifferncesbetweentables()
    Dim zDb As DAO.Database
    Dim zrs_one As DAO.Recordset
    Dim zrs_two As DAO.Recordset
    Dim zrs_ChngLog As DAO.Recordset
    Dim zfld_one As DAO.Field
    Dim zfld_two As DAO.Field
    '
    'I'd use the following to build my record sets...
    'more on that later :)
    Dim zsql As String
    '
    'Set a SINGLE pointer to the current database
    Set zDb = CurrentDb
    'Set a SINGLE pointer to each of my tables/recordsets
    Set zrs_one = zDb.OpenRecordset("tbl_people", dbOpenDynaset)
    Set zrs_two = zDb.OpenRecordset("tbl_people2", dbOpenDynaset)
    'Set zrs_ChngLog = zDb.OpenRecordset("Modification", dbOpenDynaset)
    '
    'check to see if there are records in the tables
    'to be compared
    If zrs_one.RecordCount >= 1 And zrs_two.RecordCount >= 1 Then
        'Make sure we're at the first record
        zrs_one.MoveFirst
        zrs_two.MoveFirst
        'enter the loop and execute until either of there recordsets reaches end of records
        Do
            'check to see if the value in the related fields of the record sets match and if so then check the fields
            'you really should do this in a joined query first, I've just done the equivalent here
            Do
            'keep advancing one or the other record set cursor until we match or hit eof
                Select Case zrs_one![people_pk]
                Case Is < zrs_two![people_pk]
                    zrs_one.MoveNext
                Case Is > zrs_two![people_pk]
                    zrs_two.MoveNext
                End Select
            Loop Until (zrs_one![people_pk] = zrs_two![people_pk]) Or zrs_one.EOF Or zrs_two.EOF
            'we should have the related fields equal or we're out of records, if we're not at the end then check
            If (Not zrs_one.EOF) And (Not zrs_two.EOF) Then
                'compare the field values
                For Each zfld_one In zrs_one.Fields
                    For Each zfld_two In zrs_two.Fields
                        If UCase(zfld_one.Name) = UCase(zfld_two.Name) Then
                            If zfld_one.Value <> zfld_two.Value Then
                                'here's where you could create the log table entries.... I've just sent them to the immediate window <ctrl><g>
                                Debug.Print zfld_one.Name & "_f1:=" & zfld_one.Value & " F2:=" & zfld_two.Value
                            End If
                        End If
                    Next zfld_two
                Next zfld_one
            End If
            'next record in the record sets....
            zrs_one.MoveNext
            zrs_two.MoveNext
        Loop Until zrs_one.EOF Or zrs_two.EOF
    End If
    If Not zrs_one Is Nothing Then
        zrs_one.Close
        Set zrs_one = Nothing
    End If
    If Not zrs_two Is Nothing Then
        zrs_two.Close
        Set zrs_two = Nothing
    End If
    If Not zrs_ChngLog Is Nothing Then
        zrs_ChngLog.Close
        Set zrs_ChngLog = Nothing
    End If
'!
'!VERY IMPORTAINT< Do not close the DB
'!Only free the memory, closing can cause issues
    If Not zDb Is Nothing Then Set zDb = Nothing
End Sub

As I said this is 1am work and I've been up 20 hours at this point so I'd consider the above only an outline quality. :)

When I have a bit of sleep I'll tackle the SQL version, unless you need an audit trail, the SQL would be more dynamic

**apopa** · May 16 '17, 12:56 PM

Thank you for the outline, i really appreciate it. I will try to follow and apply it to my table. I will be comparing around 50 fields with the same names in both tables.

**apopa** · May 16 '17, 02:59 PM

I just tested the code for my data and it works. Thanks so much zmbd!!
Is there a way i can store results in a new table with only the fields i want (fields that match another table list (for the fields list to be somewhat dynamic so that i don't have to change the code everytime i want more fields but just to add the name in the list and the code takes the values from there. Thank you in advance

**zmbd** · May 16 '17, 10:00 PM

It would actually make the code somewhat simpler.
However, if you do NOT need an audit trail then the additional table really isn't needed.

Create a table with a single field, set as primary key and as shorttext data type.

One option would be to loop through the two tables and check the values in the specific fields with another loop. This has a potential to error should you make a typo in the table with your fields.

one could open another recordset against the table with the field names and then loop through the other tables fairly much as before but search against the names in the fields... or even build a string and then test the field names against that using the instr() function.

To avoid the error either of those may be better ways... one could even use the tabledef object and loop through the fields in the tables to find matching records.

Several approaches using VBA for this question, I'll have to think about it.

50 fields is quite a large number in a table, is your database normalized properly?
From a purely SQL version, with only a few fields the following works quite nicely,
1) Create a query joined on the common field between the two tables, say [t_people] are the old values and [t_people_2] has the edits then:
[t_people].[PK_people] <=> [t_people_2].[PK_People]
I'd add all of the fields from both tables to the results

This would return only the records in common between both tables.

2) Create a second query based on the first (this is for simplicity's sake)

Then if we want the values from [t_people_2]
Calculated field (ie, first name)

Code:

FName_2: IIf([Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_FirstName Is Null,"ZIsNull",[Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_FirstName)

then in the 1st criteria row:

Code:

<>IIf([Q_Step_01_OnlyOnlyMatching].[tbl_people]![people_FirstName] Is Null,"ZIsNull",[Q_Step_01_OnlyOnlyMatching].[tbl_people]![people_FirstName])

For the second field, say emails

Code:

EMail_2: IIf([Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_email Is Null,"ZIsNull",[Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_email)

and in the 2nd (must be the 2nd to get the "OR" logic)

Code:

<>IIf([Q_Step_01_OnlyOnlyMatching].[tbl_people]![people_email] Is Null,"ZIsNull",[Q_Step_01_OnlyOnlyMatching].[tbl_people]![people_email])

For each field you want to check on you keep adding the calculated field.

Notice here I use "ZIsNull" this is because the fields
are text based in my example, for a numeric you would use some value that would never be entered such as -919199
HOWEVER, if you have no Null() entries (that is to say, that ALL fields of interest have a value) then this query becomes even simpler as the calculated fields do not need to be created...

I may bodge together an example database this evening, I'm out of time for right now :)

**NeoPa** · May 17 '17, 12:49 AM

Hi Apopa.

I'm sure ZMBD's suggestion follows along similar lines (without actually checking in detail - sorry.), but using pseudo-code, instead of saying :

Check field A.
If different then report and move to next record.
Otherwise move on to next field.
Continue until first difference found or none is.
Move to next record.

You would take an approach like :

Check field A
If different then log difference somewhere. A long string is one possibility.
move on to next field in the same record.
Continue until all fields exhausted.
If any problems found then all of them at once from the previously logged info.
Move to next record.

Can you see the conceptual difference between the two?

As for new questions - please ask them in a separate thread. Any answer already given is fine, but if you need to explore further then don't do it in this one otherwise such posts will be removed in conjunction with our site rules.

**apopa** · May 17 '17, 01:33 PM

Thanks for the inputs. I really appreciate it.
How can i make the table in my immediate window to an access table with some adjustments.

I get something like this in the immediate window:

ID= 013536 Change Field= Outlet Old Value= ROSYNN PO New Value= ROSSLY4N PO
ID= 013536 Change Field= Business Old Value= LOTELL DRUGS New Value= LOV3L DRUGS
ID= 013536 Change Field= BusinessType Old Value= DRUG STORE New Value= D3UG STORE

Im trying to export this to an table using an SQL statement but since its in a loop, i keep getting errors. Also is there a way i can have the ID, Change Field, Old Value, New Value as headers and the corresponding information as rows. Any suggestion is greatly appreciated.

**NeoPa** · May 17 '17, 03:30 PM

Why do you get errors when running within a loop? What errors?

Read that first question as "What haven't you told us about your setup such that straightforward additions fail when tried?". I'm guessing maybe there's some sort of unique index on the table you're trying to append to but I'm guessing without the info.

NB.
Do consider my earlier post. It can be done where each individual problem is logged to the table straight away, but may well be simpler to do as suggested there.

Please try to answer both questions clearly.

**zmbd** · May 21 '17, 07:34 AM

Example for Apopa

apopa
Attached is a simple database based upon my first code

You really need to examine the code before you run any of so that you understand what it is that you are doing. More than likely the code will only partially match what you are working with!

So to use this database unzip it somewhere convenient

Open
First thing to do is to look at the three queries
(these are based on Post#5 )
(I could have combined Step_01 with either Step_02 to make a single query; however, I've broken this down so that you can see what is happening):
Q_Step_01_OnlyO nlyMatching
Q_Step_02a_NoCh angesBetweenTab les
Q_Step_02b_Reco rdsWithChangesB etwenTables

Step_01 ensures that we're only working with records that appear in both tbl_people and tbl_people_2

Step_02a will return either only those records that do not have changes between the two tables or Step_02b will return only those that do have changes.

Because the tables have records where the fields are Null the query is a bit more complex as I said in my earlier post. The queries Step_02 will show the null value fields as "... ..." which I would be somewhat unusual to see in normal practice (I've used other combinations including "ZIsNull" as in the previous post) and, as I mentioned before, if you have no null fields then the SQL can be greatly simplified.

Step_02b is the route I would recommend as there's no bloat, you can easily build a report from it etc... and best of all it runs in a script disabled environment; HOWEVER, I noted that you said that there's some 50 fields; (@_@) thus, this might be a pain to create by hand, I'd most likely do this same thing in VBA by creating the SQL and either adding the sql to the querydefs or some sort of automation.
(also, when I created the query in the editor window, the formulas in the conditions row were under their corresponding table fields. Access split these out into their own columns during its optimization step)

Now for two versions of the code as outlined in my first post and modified per an earlier post you made (hmm)

These are only one approach to the questions and conditions you've mentioned. You really need to carefully read through the code script so that you understand what is happening at each step or you will run into issues as you try to adapt this code to your project ( Post#7 ).

Module 01_OutlineVersi onLoop
This builds on the original outline code in my first post

Module 02_SecondOutlin eVersion
This builds on the original outline code and adds your request about using a table to specify fields.

Module GlobalCode
Is exactly that, a place where a variable used by either Module_01 and Module_02 is kept and code that both of them call is placed
The code Chk4nCrtTblChng es should be straight forward to follow; however, I strongly caution about bloating the database when deleting and creating tables. I usually create a second instance of Access and create any temporary tables in that second instance so that the main database(s) stay fairly compact.

The two forms are just something I have in the template and do nothing except display the records.

>> Now, questions about this database need to be kept very narrow to stay on topic so look at your first post and consider your question.
If you're not sure the question will be on topic, simply start a new thread and paste a link back to this thread for context... extra threads are not usually an issue :)

-Z

Attached Files

BytesThread_968771.zip (106.2 KB, 322 views)

Compare Two Tables and Find Changes VBA

Compare Two Tables and Find Changes VBA

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment