By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Compare Two Tables and Find Changes VBA

P: 6
I have two tables with a unique field which i use to join the tables. I'm trying to compare the tables using access queries and some vba to find the changes within the records from the two tables. The problem is that I get the first change (field) between the tables per row rather than multiple changes within a row. For instance if the field "city" and "country" changed within a row, it only identifies one of them. I would like to have a new table with all the changes within a record. I have read about using recursive methods, any help will be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. Sub NetworkChange()
  2.  
  3. Dim fld As DAO.Field
  4.     DoCmd.SetWarnings False
  5.     DoCmd.OpenQuery "ALL_post_compare_test1", , acReadOnly
  6.     Set rs = CurrentDb.OpenRecordset("Modifications1")
  7.  
  8.     Do Until rs.EOF = True
  9.         'Perform an edit
  10.         rs.Edit
  11.         rs![Change Field] = True
  12.  
  13.             If rs("Outlet") <> rs("Old Outlet") Then
  14.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(2) ['Old Outlet'];")
  15.             ElseIf rs("Business") <> rs("Old Business") Then
  16.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(3).NAME
  17.             ElseIf rs("Address") <> rs("Old Address") Then
  18.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(4).NAME  
  19.             ElseIf rs("City") <> rs("Old City") Then
  20.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(5).NAME  
  21.             ElseIf rs("Prov") <> rs("Old Prov") Then
  22.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(6).NAME  
  23.             ElseIf rs("Pcode") <> rs("Old Pcode") Then
  24.                 rs("Change Field") = CurrentDb.TableDefs("Modifications1").Fields(7).NAME  
  25.             ElseIf rs("Phone") <> rs("Old Phone") Then
  26.                 rs("Change Field") = CurrentDb.TableDefs("Modifications").Fields(8).NAME 
  27.             Else: rs("Change Field") = "-"
  28.             End If
  29.             rs.Update
  30.     Loop
May 15 '17 #1

✓ answered by zmbd

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)

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub logdifferncesbetweentables()
  5.     Dim zDb As DAO.Database
  6.     Dim zrs_one As DAO.Recordset
  7.     Dim zrs_two As DAO.Recordset
  8.     Dim zrs_ChngLog As DAO.Recordset
  9.     Dim zfld_one As DAO.Field
  10.     Dim zfld_two As DAO.Field
  11.     '
  12.     'I'd use the following to build my record sets...
  13.     'more on that later :)
  14.     Dim zsql As String
  15.     '
  16.     'Set a SINGLE pointer to the current database
  17.     Set zDb = CurrentDb
  18.     'Set a SINGLE pointer to each of my tables/recordsets
  19.     Set zrs_one = zDb.OpenRecordset("tbl_people", dbOpenDynaset)
  20.     Set zrs_two = zDb.OpenRecordset("tbl_people2", dbOpenDynaset)
  21.     'Set zrs_ChngLog = zDb.OpenRecordset("Modification", dbOpenDynaset)
  22.     '
  23.     'check to see if there are records in the tables
  24.     'to be compared
  25.     If zrs_one.RecordCount >= 1 And zrs_two.RecordCount >= 1 Then
  26.         'Make sure we're at the first record
  27.         zrs_one.MoveFirst
  28.         zrs_two.MoveFirst
  29.         'enter the loop and execute until either of there recordsets reaches end of records
  30.         Do
  31.             'check to see if the value in the related fields of the record sets match and if so then check the fields
  32.             'you really should do this in a joined query first, I've just done the equivalent here
  33.             Do
  34.             'keep advancing one or the other record set cursor until we match or hit eof
  35.                 Select Case zrs_one![people_pk]
  36.                 Case Is < zrs_two![people_pk]
  37.                     zrs_one.MoveNext
  38.                 Case Is > zrs_two![people_pk]
  39.                     zrs_two.MoveNext
  40.                 End Select
  41.             Loop Until (zrs_one![people_pk] = zrs_two![people_pk]) Or zrs_one.EOF Or zrs_two.EOF
  42.             'we should have the related fields equal or we're out of records, if we're not at the end then check
  43.             If (Not zrs_one.EOF) And (Not zrs_two.EOF) Then
  44.                 'compare the field values
  45.                 For Each zfld_one In zrs_one.Fields
  46.                     For Each zfld_two In zrs_two.Fields
  47.                         If UCase(zfld_one.Name) = UCase(zfld_two.Name) Then
  48.                             If zfld_one.Value <> zfld_two.Value Then
  49.                                 'here's where you could create the log table entries.... I've just sent them to the immediate window <ctrl><g>
  50.                                 Debug.Print zfld_one.Name & "_f1:=" & zfld_one.Value & " F2:=" & zfld_two.Value
  51.                             End If
  52.                         End If
  53.                     Next zfld_two
  54.                 Next zfld_one
  55.             End If
  56.             'next record in the record sets....
  57.             zrs_one.MoveNext
  58.             zrs_two.MoveNext
  59.         Loop Until zrs_one.EOF Or zrs_two.EOF
  60.     End If
  61.     If Not zrs_one Is Nothing Then
  62.         zrs_one.Close
  63.         Set zrs_one = Nothing
  64.     End If
  65.     If Not zrs_two Is Nothing Then
  66.         zrs_two.Close
  67.         Set zrs_two = Nothing
  68.     End If
  69.     If Not zrs_ChngLog Is Nothing Then
  70.         zrs_ChngLog.Close
  71.         Set zrs_ChngLog = Nothing
  72.     End If
  73. '!
  74. '!VERY IMPORTAINT< Do not close the DB
  75. '!Only free the memory, closing can cause issues
  76.     If Not zDb Is Nothing Then Set zDb = Nothing
  77. 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

Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,397
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)

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub logdifferncesbetweentables()
  5.     Dim zDb As DAO.Database
  6.     Dim zrs_one As DAO.Recordset
  7.     Dim zrs_two As DAO.Recordset
  8.     Dim zrs_ChngLog As DAO.Recordset
  9.     Dim zfld_one As DAO.Field
  10.     Dim zfld_two As DAO.Field
  11.     '
  12.     'I'd use the following to build my record sets...
  13.     'more on that later :)
  14.     Dim zsql As String
  15.     '
  16.     'Set a SINGLE pointer to the current database
  17.     Set zDb = CurrentDb
  18.     'Set a SINGLE pointer to each of my tables/recordsets
  19.     Set zrs_one = zDb.OpenRecordset("tbl_people", dbOpenDynaset)
  20.     Set zrs_two = zDb.OpenRecordset("tbl_people2", dbOpenDynaset)
  21.     'Set zrs_ChngLog = zDb.OpenRecordset("Modification", dbOpenDynaset)
  22.     '
  23.     'check to see if there are records in the tables
  24.     'to be compared
  25.     If zrs_one.RecordCount >= 1 And zrs_two.RecordCount >= 1 Then
  26.         'Make sure we're at the first record
  27.         zrs_one.MoveFirst
  28.         zrs_two.MoveFirst
  29.         'enter the loop and execute until either of there recordsets reaches end of records
  30.         Do
  31.             'check to see if the value in the related fields of the record sets match and if so then check the fields
  32.             'you really should do this in a joined query first, I've just done the equivalent here
  33.             Do
  34.             'keep advancing one or the other record set cursor until we match or hit eof
  35.                 Select Case zrs_one![people_pk]
  36.                 Case Is < zrs_two![people_pk]
  37.                     zrs_one.MoveNext
  38.                 Case Is > zrs_two![people_pk]
  39.                     zrs_two.MoveNext
  40.                 End Select
  41.             Loop Until (zrs_one![people_pk] = zrs_two![people_pk]) Or zrs_one.EOF Or zrs_two.EOF
  42.             'we should have the related fields equal or we're out of records, if we're not at the end then check
  43.             If (Not zrs_one.EOF) And (Not zrs_two.EOF) Then
  44.                 'compare the field values
  45.                 For Each zfld_one In zrs_one.Fields
  46.                     For Each zfld_two In zrs_two.Fields
  47.                         If UCase(zfld_one.Name) = UCase(zfld_two.Name) Then
  48.                             If zfld_one.Value <> zfld_two.Value Then
  49.                                 'here's where you could create the log table entries.... I've just sent them to the immediate window <ctrl><g>
  50.                                 Debug.Print zfld_one.Name & "_f1:=" & zfld_one.Value & " F2:=" & zfld_two.Value
  51.                             End If
  52.                         End If
  53.                     Next zfld_two
  54.                 Next zfld_one
  55.             End If
  56.             'next record in the record sets....
  57.             zrs_one.MoveNext
  58.             zrs_two.MoveNext
  59.         Loop Until zrs_one.EOF Or zrs_two.EOF
  60.     End If
  61.     If Not zrs_one Is Nothing Then
  62.         zrs_one.Close
  63.         Set zrs_one = Nothing
  64.     End If
  65.     If Not zrs_two Is Nothing Then
  66.         zrs_two.Close
  67.         Set zrs_two = Nothing
  68.     End If
  69.     If Not zrs_ChngLog Is Nothing Then
  70.         zrs_ChngLog.Close
  71.         Set zrs_ChngLog = Nothing
  72.     End If
  73. '!
  74. '!VERY IMPORTAINT< Do not close the DB
  75. '!Only free the memory, closing can cause issues
  76.     If Not zDb Is Nothing Then Set zDb = Nothing
  77. 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
May 16 '17 #2

P: 6
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.
May 16 '17 #3

P: 6
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
May 16 '17 #4

zmbd
Expert Mod 5K+
P: 5,397
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)
Expand|Select|Wrap|Line Numbers
  1. FName_2: IIf([Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_FirstName Is Null,"ZIsNull",[Q_Step_01_OnlyOnlyMatching].tbl_people_2!people_FirstName)
  2.  
then in the 1st criteria row:
Expand|Select|Wrap|Line Numbers
  1. <>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
Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. <>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 :)
May 16 '17 #5

NeoPa
Expert Mod 15k+
P: 31,418
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 :
  1. Check field A.
  2. If different then report and move to next record.
  3. Otherwise move on to next field.
  4. Continue until first difference found or none is.
  5. Move to next record.
You would take an approach like :
  1. Check field A
  2. If different then log difference somewhere. A long string is one possibility.
  3. move on to next field in the same record.
  4. Continue until all fields exhausted.
  5. If any problems found then all of them at once from the previously logged info.
  6. 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.
May 17 '17 #6

P: 6
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.
May 17 '17 #7

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 17 '17 #8

zmbd
Expert Mod 5K+
P: 5,397
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_OnlyOnlyMatching
Q_Step_02a_NoChangesBetweenTables
Q_Step_02b_RecordsWithChangesBetwenTables

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_OutlineVersionLoop
This builds on the original outline code in my first post

Module 02_SecondOutlineVersion
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 Chk4nCrtTblChnges 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
File Type: zip BytesThread_968771.zip (106.2 KB, 66 views)
May 21 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.