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

 

[HitechUser]

cliffhanger9,

A simple way is to create an empty MDB. Then create table links to the table in each database. Then use the unmatched query wizard and compare both ways. (i.e. table1 in old mdb to table1 in new mdb then table1 in new mdb to table1 in old mdb.)

Please note linked table name must be unique.

Also this sample may provide a different solution for you. Compare

I hope this will help...



[thumbsup2}

 

[willir]

...and sometimes, it helps to include date fields for when records were created, especially for a transaction table.

 

[BNPMike]

You might have put your finger on it. Dump them to text and use Word.

I do it manually. Just a sequence of queries selecting records where oldtable.fieldn <> newtable.fieldn and a few null tests where appropriate. You need a bit of cutting and pasting to add the changed fieldname eveytime you move to the next column, but it's quicker than looking at things visually.

 

[cliffhanger9]

Thanks BNPMike but I am afraid that is an unrealistic way to go about this project. I have over 120 tables in each database. That is alot of copying and pasting to Word.

Hitech, same thing. Unless there is a quick and easy way to create alot of linked tables from 2 different databases, that is a lot of manual linking

If you know an easy way to link alot of tables, then that could work, I just dont know of any such way.

 

[willir]

You may have two databases with two tables, but...
- Are you looking for design changes?
- Are you looking for the most current data?

If you are looking at design changes...
I like HitechUser suggestion with a twist. After linking your tables, instead of the unmatched queries, use the Relationship tool. (from the menu, "Tools" -> "Relationships")

Add matching tables from each database side by side. (Hint: Might be easier to easily reference each table with a different name, eg DB1 tblContact -> tblContact1, DB2 tblContact -> tblContact2) This way you can visually compare fields. This wont allow you to look for differences within the specific fields, but you can more easily look for different fields.

You can also run the Documenter utility under the "Tools" menu for a more detailed view.


If you are looking at differences in data:
Focus on the transaction tables where a high volume of data is written.

I am not aware of a tool that does all the comparison work for you. Grun work and elbow grease may be required.

 

[HitechUser]

If you know an easy way to link alot of tables, then that could work

Actually yes there is an easy way to link alot of tables. Select the table objects and use the TransferDatabase method in a loop.

' Code not complete.... PUT this in a loop!!!
' User must provide the value for dbPath
' 
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

' Create link to old MDB (link name must be unique
DoCmd.TransferDatabase acLink, "Microsoft Access", dbPath, acTable, MSysObjects.Name, "MSysOjbect.Name" & "old"

' ..... Loop....

I would provide a more complete code example, but it is a very busy day already!!!