Compare two tables for differences.

[barim]

I have two databases: Database1 and Database2.

Database1 is a database that stores all products information and is refreshed every day. Now, I have Database2 that has products information but is not refreshed at all. Tables from both databases are identical and have all the same labels. This first column of each table and each database has unique Product ID number which should not be changed at all. These numbers are fixed and I need to use them to search data in Database1. All other record fields could change in Database1 but not in Database2. I need to know which fields in the record from Database1 changed comparing to fields in the record from Database2. So, my Product ID is the first column, product type is the second column, product description is third and so on. If something changes in any of these fields (Product ID is exception), I want these records pulled into separate table and if possible highlight fields that are different, or where changes occurred.

Thanks.

 

[xenou]

First step would be to get both tables together so you can join and query on them. In one of the DB's you'll need to pull in the corresponding table as a linked table. That way you can query both tables in the same database. Technically, I think you can also join on a table in an external database using a [FROM table IN {filepath}] clause in your select query ... but it will still be easier to write the query and test it, originally, using a simple linked table.

 

[Micron]

What you have to consider is, is it possible for you to alter data in db1 when you have linked to it? If so, very dangerous - maybe not from any mistake you might make, but anyone else who could open db2. It would not be the first time someone thought they were affecting copies of tables when in fact they were not due to linking. You have not said that either of these db's are Access (although you've posted to the Access part of the forum, it's no guarantee). If so and if db1 can be affected by db2, it might be better to push updates to db2, which would need a field in any updating tables to flag that there was an update. I'd consider using a date field for that, regardless of how the updates are done.