Compare Tables in Access

[itsmedd]

Hi,
I need to compare 2 tables with Same Structure in a Database and pull out records that are not matching in both the tables. As anyone done similar task in Access 2002, if so please let me know how this can be accomplised.

 

[pbaldy]

Play with the Find Unmatched query wizard.

 

[psu_1995]

I also have a need to compare two data tables. The unmatched query wizard is fine if you're trying to pull out extra records from one table as it only allows one comparison.

However, I need a query which will compare every column in a record (a record does have an identical key in both tables) to the other table and spit out any discrepancies between columns in the records (my records have about 50 columns each).

Can this be done with a query through the gui?

Rob

 

[The_Doc_Man]

A query can do this but it will be a pain in the toches to set up.

You need to think a bit more about how you want to format the results. A query could easily be written that JOINS the two tables and compares field contents to spit out something for any cases that don't match. But the mapping of this sort of thing would become a nightmare quickly. If you have 50 fields, you have 50 possible outputs.

I might do something really bizarre here - rather than break this down into one query fits all, I might write something that somehow steps through the two tables to "stretch" them out into a linear thread. If you are going to do this often, it might be a good idea to automate this.

Suppose (for argument's sake) you have:

TableA: PK, FieldU, FieldV, FieldW, FieldX, FieldY, FieldZ

TableB: PK, FieldU, FieldV, FieldW, FieldX, FieldY, FieldZ

I might FIRST run two queries - one an Append, the other an UPDATE.

TableC: PK, FieldName, TableAValue, TableBValue, Matching (Y/N)

Table C might contain records like number, FieldU, UValueA, UValueB, YES

where I stored the Key, Field Name, and value for every field in every record in table A. THEN I go back and update TableC to show the value for TableB - and at that point I can do the comparison. To account for data type differences, I would use TableAValue and TableBValue as strings and if the field types aren't strings, use the CStr$() function to store them as such. From there you can let a report wizard build the discrepancy report.

I'll admit that if you aren't comfortable with VBA, this might not be for you, but there are ways to step through the fields in a recordset and there are ways to build dynamic strings that become queries when executed. It is what I would do if given your problem. But that might be more work than you wanted. It depends on how often you need to do this. Very often? Spend some time automating it. Once only? That's the worst case and you have to decide how badly you need it.

 

[psu_1995]

Thanks for the reply - I think I follow what you're suggesting. I do have to do this every week on large amounts of data, so I guess I'll be using VBA to automate it.

I'm quite proficient in Excel VBA, but am new to Access. I guess I'll be heading to the nearest bookstore now to grab a Access VBA book so I can try to implement something similar to what you've described.

Really appreciate the suggestion because I didn't want to plunge into writing a script if I didn't have to.

It's also hard to believe that no one else has already done this (and shared it!).

Rob

 

[itsmedd]

Thanks a lot for all the suggestions...PSu pls let me know if you happen to accomplish this VBA script as i would also need this code and i am not that comfortable with Code.Appreciate all the suggestions provided.

 

[The_Doc_Man]

I've found a book for VBA that isn't too bad -

Access 2003 VBA (sub-title: Programmer's Reference) by Cardoza, Hennig, Seach, and Stein - WROX (imprimateur of Wiley & Sons).

No book is perfect, but this one tries to avoid nonsense examples to explain what it wants to accomplish. It is generally intelligent in its approach to any problem. Of course, for folks not comfortable with VBA in the first place, it might be hard to "map" your problem to any of their solutions. The problem there is that you really don't have the experience needed to correctly find the analogies in their code to your problems. Still, it isn't too bad.

 

[WayneRyan]

IM,

I was toying with this a while ago. My sample is pretty rough, but the
principle should be good.

http://www.access-programmers.co.uk/forums/showthread.php?t=127628

You MUST HAVE A PRIMARY KEY(s).
It will only handle 20 fields.

At least it's a start.

Wayne

 

[psu_1995]

Well about a month has passed and I've written a ton of VBA Access code and automated almost my entire process, so I think I'm ready to tackle this more complicated issue. Thanks for the feedback and the book suggestions. I now own three books, each complimenting the others.

Wayne - I noticed your post on many pages where comparison questions have been asked. I tried your code a while ago and it doesn't run. Unless you've changed it recently (or I'm crazy), you've got a bug in there which prevents it from running. Just thought you might like to know as you propagate your message.

Rob

 

[WayneRyan]

Rob,

By definition you can't detect the Inserts/Deletes without Primary Keys.

Sorry,
Wayne

 

[psu_1995]

Wayne,
My bad - I tried your code on a different database and the code worked as advertised.

I do need to build something that will compare two tables within a database, and your automation has given me some ideas - thanks for that!

Rob

 

[DCrake]

Simple Software Solutions

Hi
Joining this thread late on, I know, but can I make a suggestion.

Create a function in a new module

Add the following logic

Create 2 recordsets RsOld and RsNew

Working on the assumption that both tables contain the same field names and the same number of fields

Dim FldName as String

Set RsOld = CurrentDb.OpenRecordSet("Old Table")
Do Until RsOld.EOF
Set RsNew = CurrentDb.OpendRecordSet("Select * From New Table Where PK ='" & RsOld("PK") & "'")


If Not RsNew.EOF And Not RsNew.BOF Then

For x = 0 To RsOld.Fields.Count -1
fldName = RsOld(x).Name
If RsOld(fldName) <> RsNew(fldName) Then
....Contents of field has changed ... Do Something Here
Endif
Next x​

Else

.... New table has no matching PK ? Deleted
.... Do something else here​

End If

RsOld.MoveNext

Loop

RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing


This will loop through each field in each record in the first table and compare it to the same field in the new table via the PK.

Only thing to consider is the error coding for Null Values in either tables and fields therein.

The advantage of this is that if you add new fields to the tables you do not need to worry about the ordinal position matching in both tables.

You could have a third table that contains the fields
PK
FldName
OldValue
NewValue
DateDetected

Now when a mismatch is detected you can post the differences to the table which shows the PK (Duplicates Ok), The name of the field that has changed, What the value was prior to change, what the new value is and what date you ran this procedure. Therefore you will end up with full traceability for all your data changes.

Code Master

 

[psu_1995]

DCrake,

Thanks for the suggestion! I'm going to tackle this code next week so I'll probably incorporate a bit of what you've suggested.

Rob

 

[kelvin78]

Re: Simple Software Solutions

Hi,

Is it possible to share a sample database to show how exactly this works with two recordsets and a third table that shows the differences?

I'm very new to VBA and have done some basic functions but I still can't get around understanding your sample code below.

Would appreciate your kind help as it'll save me a ton of time.


Kelvin

Hi
Joining this thread late on, I know, but can I make a suggestion.

Create a function in a new module

Add the following logic

Create 2 recordsets RsOld and RsNew

Working on the assumption that both tables contain the same field names and the same number of fields

Dim FldName as String

Set RsOld = CurrentDb.OpenRecordSet("Old Table")
Do Until RsOld.EOF
Set RsNew = CurrentDb.OpendRecordSet("Select * From New Table Where PK ='" & RsOld("PK") & "'")


If Not RsNew.EOF And Not RsNew.BOF Then

For x = 0 To RsOld.Fields.Count -1
fldName = RsOld(x).Name
If RsOld(fldName) <> RsNew(fldName) Then
....Contents of field has changed ... Do Something Here
Endif
Next x​

Else

.... New table has no matching PK ? Deleted
.... Do something else here​

End If

RsOld.MoveNext

Loop

RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing


This will loop through each field in each record in the first table and compare it to the same field in the new table via the PK.

Only thing to consider is the error coding for Null Values in either tables and fields therein.

The advantage of this is that if you add new fields to the tables you do not need to worry about the ordinal position matching in both tables.

You could have a third table that contains the fields
PK
FldName
OldValue
NewValue
DateDetected

Now when a mismatch is detected you can post the differences to the table which shows the PK (Duplicates Ok), The name of the field that has changed, What the value was prior to change, what the new value is and what date you ran this procedure. Therefore you will end up with full traceability for all your data changes.

Code Master

 

[pbaldy]

Kelvin, unfortunately David is no longer with us. Did you try Wayne's sample?

 

[kelvin78]

My apologies as I wasn't aware of this. I'll try with the earlier sample and see if it works out.

Kelvin, unfortunately David is no longer with us. Did you try Wayne's sample?

 

[nhtuan]

It works for me with modification, thank DCrack for sharing,

Private Sub Command1_Click()
Dim FldName As String

Set RsOld = CurrentDb.OpenRecordset("tabla")
Do Until RsOld.EOF
Set RsNew = CurrentDb.OpenRecordset("Select * From tablb Where tablb.manghiencu ='" & RsOld("manghiencu") & "'")
If Not RsNew.EOF And Not RsNew.BOF Then
For x = 0 To RsOld.Fields.Count - 1
FldName = RsOld(x).Name
If RsOld(FldName) <> RsNew(FldName) Then
CurrentDb.Execute "INSERT INTO resulttab(PKey, tenbien, myoldval, mynewval) VALUES ('" & RsOld("manghiencu") & "','" & FldName & "' ,'" & RsOld(FldName) & "', '" & RsNew(FldName) & "')"
ElseIf IsNull(RsOld(FldName)) And Len(RsNew(FldName)) > 0 Or RsOld(FldName) = "" And Len(RsNew(FldName)) > 0 Then
CurrentDb.Execute "INSERT INTO resulttab(PKey, tenbien, myoldval, mynewval) VALUES ('" & RsOld("manghiencu") & "','" & FldName & "' ,'" & " " & "', '" & RsNew(FldName) & "')"
ElseIf Len(RsOld(FldName)) > 0 And IsNull(RsNew(FldName)) Or Len(RsOld(FldName)) > 0 And RsNew(FldName) = "" Then
CurrentDb.Execute "INSERT INTO resulttab(PKey, tenbien, myoldval, mynewval) VALUES ('" & RsOld("manghiencu") & "','" & FldName & "' ,'" & RsOld(FldName) & "', '" & " " & "')"
'....Contents of field has changed ... Do Something Here
End If
Next x
Else
'.... New table has no matching PK ? Deleted
'.... Do something else here
End If

RsOld.MoveNext

Loop

RsOld.Close
RsNew.Close
Set RsOld = Nothing
Set RsNew = Nothing
End Sub