Compare data in 2 Access Databases with exactly the same structure

[dragonsel]

I have had a search through the forums but haven't found exactly the answer I need.

I want to compare the data in 2 Access databases in multiple tables. Does anyone know of a tool like the RedGate SQL compare tool for Access? I could have sworn there was a compare database tool in Access but I haven't been able to find it.

If I have to do it the long way (i.e. write a function) does anyone have any ideas on how to start it?

Background Info:
The data in an Access database was split into 2 separate databases (exactly the same structure). Both databases have been updated with different data. I am now trying to put the 2 databases back to 1.

I have created an insert statement to insert the records of the source table into the destination table but this does not allow for changes that have been made to existing records or possible conflicting records (I need to know details about the latter)

The SQL insert statement generated from a procedure (as this has to be done for 17 different tables) is:

INSERT INTO tblAppraisal([Appraisal ID], [Personal ID], [Date], [Appraiser], [Appraisal Action], [Conditions], [Reviewed], [Reviewer], [Comments], [Action Achieved], [Appraisal Case], [Review Case])
SELECT [Appraisal ID], [Personal ID], [Date], [Appraiser], [Appraisal Action], [Conditions], [Reviewed], [Reviewer], [Comments], [Action Achieved], [Appraisal Case], [Review Case]
FROM tblAppraisal1

and it works fine purely inserting records. So now I just have to figure out how to make sure it doesn't write over any existing data that may have the same primary key.

Appreciate any help
Thanks,
Selene

 

[lameid]

Interesting problem. Since nobody jumped on this, I have one thought.

It sounds like you have a tool that does what you want for SQL server. Why not just upsize the databases to SQL server, manipulate them there and then import everything back to Access? Better still, once you have an SQL database that is correct, import all the definitions from one Access DB to another and then APPEND the data from SQL server.

 

[dalenz]

dragonsel,

I have either linked or imported the old tables and then its a two stage procedure.

1. Use the find unmatched query wizard (or write code) for records that are in the old table but not the new.These can then be appended to the table in the current DB.

2. Use the find duplicate query wizard (or write code) for records in both tables and then append these with a new key.

Perhaps not the most elegant method but did the job.