Solved

Comparing Access database tables

Posted on 2008-10-14
7
367 Views
Last Modified: 2012-05-05
What we have is two databases on seperate CD's, within those databases are tables that we need to compare from one database CD to the other database CD.  The column headings match, we are trying to see the differences in the data in each individual field between the two database tables.  Are there any tools that may accomplish this I have tried to search for some and have actually tried ones out, but they were unsuccessfull.  Any help on this would be great thankyou
0
Comment
Question by:Pvteye
7 Comments
 
LVL 15

Expert Comment

by:dbase118
ID: 22712163
How many fields are you looking to compare from each table? Im sure someone will post some handy code for you to use, but I have done this on a relatively small scale using the conditional formatting features. This would assume you could get both tables into one file.  Example:

Table 1
Employee ID
First Name
Last Name
City
State

Table 2
Employee ID
First Name
Last Name
City
State

Make a query with Employee ID then pull each field from both tables. In essence you end up with a query that has the unique identifier and then both data items for each field. You can then go into the conditional formatting and write syntax like IF Table2. First Name is not equal to Table1. First Name turn it red. If you write that for each of your fields it will essentially highlight everywhere that the tables differ in data.

As I said if you are looking at comparing a lot of field this may not be a feasable option due to time, but it workss OK on a small scale

0
 

Expert Comment

by:culminIT
ID: 22712406
Are you looking for a way to automatically run through the tables and compare the data?  This process can be simplified if you can guarantee that the fieldnames match 100%, and there is always one common field that you expect the data to be the same (eg. the unique ID or code).
If this is the case, you can loop through the table definitions of one database.  Create a select that joins the two tables on the common field, then loop through the other fields and compare the two tables.  You will also need to do left and right joins where the records don't exist in one or the other.
0
 

Author Comment

by:Pvteye
ID: 22713909
There are 40 or so field headings and about 190,000 lines of data to be compared, not sure if an automated tool would be better for this or some sort of code for joining the tables and things like that?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Accepted Solution

by:
culminIT earned 500 total points
ID: 22728969
If its only the two tables you want to compare to each other, I would recommend firstly creating 2 outer join queries to check for non-existence on both sides:
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.CommonField = Table2.CommonField WHERE Table2.CommonField Is Null;
SELECT Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.CommonField = Table2.CommonField WHERE Table1.CommonField Is Null;
For differences on the matched inner join, you can do the following:
Create a new table (eg. tmp) with a field for the fieldname (FieldName), Value in table 1 (OldValue) and table 2 (NewValue).  Then you can try and use the code snippet I attached.
Hope that helps!  

Public Sub CompareData()
 

  Dim rstCompare As Recordset, rstDifferences As Recordset

  Dim iRecordNum As Long, sField As String

  Dim iFieldNum As Integer, sName As String

  Dim OldValue As Variant, NewValue As Variant

  

  On Error GoTo ErrHandler

  

  Set rstCompare = CurrentDb.OpenRecordset("SELECT * FROM Table1 INNER JOIN Table2 ON Table1.CommonField = Table2.CommonField;", dbOpenSnapshot)

  Set rstDifferences = CurrentDb.OpenRecordset("tmp", dbOpenDynaset)
 

    iRecordNum = 0

    With rstCompare

      If Not .EOF Then

        .MoveFirst

        While Not .EOF

          iRecordNum = iRecordNum + 1

          For iFieldNum = 0 To .Fields.Count - 1

            sField = .Fields(iFieldNum).Name

            'Change this length to match your first table name

            If Left(sField, 7) = "Table1." Then

              sName = Right(sField, Len(sField) - 7)

              ' Columns to ignore

              If sName <> "CommonField" Then

                OldValue = .Fields("Table2." & sName)

                NewValue = .Fields(sField)

                If IsNull(OldValue) Then OldValue = "Null"

                If IsNull(NewValue) Then NewValue = "Null"

                

                If OldValue <> NewValue Then

                  

                  rstDifferences.AddNew

                    rstDifferences!FieldName = sName

                    rstDifferences!OldValue = OldValue

                    rstDifferences!NewValue = NewValue

                  rstDifferences.Update

                End If

              End If

            End If

          Next

          .MoveNext

        Wend

      End If

    End With

    

    'Clean up recordsets

    rstCompare.Close

    rstDifferences.Close

    Set rstCompare = Nothing

    Set rstDifferences = Nothing

    

  On Error GoTo 0

  

  Exit Sub
 

ErrHandler:

  'Add some error handling

  Resume Next
 

End Sub

Open in new window

0
 
LVL 1

Expert Comment

by:NO_CARRIER
ID: 23105300
I'm sorry to bring up this PAQ's question, but I am also attempting to do the same and follow the same solution... I'm just not sure what is meant by:
"Create a new table (eg. tmp) with a field for the fieldname (FieldName), Value in table 1 (OldValue) and table 2 (NewValue).  Then you can try and use the code snippet I attached."

I have done both check queries (outer joins), and no queries have been returned.
0
 
LVL 1

Expert Comment

by:NO_CARRIER
ID: 23105805
Nevermind, I figured it out... just had to read the code to understand.. :)
Thanks for the solution.
0
 

Expert Comment

by:culminIT
ID: 23119093
No problem, glad it helped.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now