How to Compare Two Database's Data...?

[CliffHanger9]

Hello all-

I need to compare two different datasets of identical databases. BY this i mean that I have two databases with identical structure (tables, fieldnames etc) however in one of the databases, some of the data has changed.

I need to see the differences in what has changed similar to the Compare Coduments functionality of Microsoft Word.

Has anyone done this? or any suggestions or referances on how to go about it?

Any help would be greatly appreciated!!!

thanks in advance

 

[sfreeman@co.mer]

I think I would try to link from one db to the other, attaching to the tables that I wanted to check. Then create a 'Find Unmatched' query to determine what data existed in one table that did not exist in the other.

With a table named My_Table in the first db and My_Table1 in the 2nd db, and Field names of A, B, C & D,

The SQL would look something like:

SELECT
My_Table.A
, My_Table.B
, My_Table.C
, My_Table.D

FROM
My_Table LEFT JOIN My_Table1 ON (My_Table.C = My_Table1.C)
AND (My_Table.B = My_Table1.B)
AND (My_Table.A = My_Table1.A)
AND (My_Table.D = My_Table1.D)

WHERE
(((My_Table1.D) Is Null));


HTH

 

[WayneRyan]

Cliff,

I've done quite a bit of this in the SQL server realm. Basically, I used the
system tables SysObjects and SysColumns to generate the SQL scripts to
check for Inserts/Deletes/Updates.

It works great! Couple hundred tables, 300K+ rows, couple of minutes.

I think that if it was mapped to Access, it would be QueryDefs instead of
stored procedures, but the end result should be the same.

How serious are you?

Wayne

 

[CliffHanger9]

Thanks sfreeman - unfortunately I have at least 120 tables in this thing and unless there is a way to automate linking them this will be very tedious

I have a very cool script that compares two tables to a decent level of detail but again this is at the database level vs the table level

Cliff,

I've done quite a bit of this in the SQL server realm. Basically, I used the
system tables SysObjects and SysColumns to generate the SQL scripts to
check for Inserts/Deletes/Updates.

It works great! Couple hundred tables, 300K+ rows, couple of minutes.

I think that if it was mapped to Access, it would be QueryDefs instead of
stored procedures, but the end result should be the same.

How serious are you?

Wayne

How serious am I? I m not sure what you mean by that but it is something that has to be done. Just for clarification before we get to far, you are talking about comparing entire databases right? Like I said I have two databases one is basically a backup of the other but one of them has been edited and i need to be able to see what changes were made to this one.

I dont know much about QueryDefs but I would be very interested in your process if you wouldnt mind sharing..

Thanks in advance!!!!!!!

 

[WayneRyan]

Cliff,

Some background on my efforts. We had several SQL Server databases that
had the same structure, but different data. The typical database consisted
of about 160 tables, with a total of about 300,000 rows. I used an Access
front-end to examine them and was surprised at how fast it ran (~ 2 minutes).

My approach was:

1) Go to the Master Database & retrieve all DB Names, the user selects
a Source & Target Database Name.

2) Go to SysObjects for one of the databases & retrieve all of the
table names.

3) For each table use SysColumns to generate three Stored Procedures
to send to the Server: CheckInsert, CheckDelete and CheckUpdate.
I'm on break from a VB.NET class, so I'll post a sample later tonight.

4) Execute the three scripts.

This worked real well in the SQL server environment, but Access databases
present a few technical changes. I'd modify the process as follows:

1) I'd make a copy of Database A. Then, I'd link all of the tables in
Database B. The end result would be that each table in Database A would
have its twin with a "1" appended to its name.

2) Use the TableDefs collection to get all of the table names.

3) For each table, use the FieldDefs collection to get all of the field
names.

4) For each table, write three QueryDefs to check for the data.

5) Run the QueryDefs.

Notes:
======

1) To check for the Deletes/Inserts, you'll have to know the Primary
Key(s) for that table. That's why you'll need different queries to
check for the Addition/Omission of the PKs between the two tables.

2) Knowing the Primary Keys for the tables will let you REPORT the
results into some kind of Transaction table. You have to convey:
Table --> Action --> Primary Key(s).

3) I'd expect the Access environment (Jet) to be a lot slower. I got
away with using (Select ... Where Not In ...). To make it easier for
Jet, you'll just have to do Joins where "B.PK Is Null".

The reason that I said "How serious are you?" is that the effort requires
a good bit of learning; VBA coding, queries for detection/reporting, and
knowledge of TableDefs, FieldDefs, QueryDefs, etc.

I'd be happy to help you through the process.

Will check in on you later.

Wayne

 

[sfreeman@co.mer]

Sounds like you guys got this under control, but as for your statement that linking alot of tables would be tedious, not so.

You can link to as many tables as you wish in seconds.

The more tedious part may be creating the 'find unmatched' queries, but using vba you should be able to setup a loop to do this for you.

Just an FYI.