Compare tables between two databases

[PuddinPie]

Hello,

I’m back again. This time I want to compare two tables in two different databases. I have an archive database and I want to make sure that both tables are equal before I delete the information from the production databases table. I’ve been looking around and have been finding that I should be using a UNION query but I don’t know how.
Lets I have B_Table in the Archive database and A_Table in the Production database. I need it to compare and let me know if there is anything that does not match but without linking tables or anything like that.

Thanks.

 

[MSAccessRookie]

Hello,

I’m back again. This time I want to compare two tables in two different databases. I have an archive database and I want to make sure that both tables are equal before I delete the information from the production databases table. I’ve been looking around and have been finding that I should be using a UNION query but I don’t know how.
Lets I have B_Table in the Archive database and A_Table in the Production database. I need it to compare and let me know if there is anything that does not match but without linking tables or anything like that.

Thanks.

Is there any reason that you could not create a Link From the Front End of your Production Database to the Tables in the Archive Database? Then it becomes as simple as creating a Query between two tables to seek similarities and differences.

The UNION Query would look something like this:

SELECT ProductionTable LEFT JOIN ArchiveTable 
ON ProductionTable.TableKey = ArchiveTable.TableKey
WHERE  ArchiveTable.TableKey IS NULL
UNION SELECT ArchiveTable LEFT JOIN ProductionTable ON 
ArchiveTable.TableKey = ProductionTable.TableKey 
WHERE  ProductionTable.TableKey IS NULL

Any records that contain NULL values represent missing data in one of the two Tables.

 

[MarkK]

If I had to make sure that the archive table was exactly the same as some current table, I would delete the archive table, and copy the current table there in it's place.

 

[PuddinPie]

The database is getting to an ouragous size with all of the data in the tables (Over a gig) I don't want to link them because it will just make it bigger. I'm wanting to have the PROD database archive the tables, check them to make sure they were archived correctly and then delete the data in the PROD tables. When the EU needs the archived data they can unacrhive just as needed. There in keeping the PROD database small and moving quickly.