Tool to compare two access databases

  • Thread starter Thread starter Partha Mandayam
  • Start date Start date
P

Partha Mandayam

Is there any tool which can compare two Access databases, report the
differences between them and copy the data for matching fields between the
two if one is empty?
 
Is there any tool which can compare two Access databases, report the
differences between them and copy the data for matching fields between the
two if one is empty?
Click to expand...

Insofar as comparisons are concerned, I highly recommend FMS, Inc.'s "Total
Access Detective". Information on their product is available at:

http://www.fmsinc.com/products/detective/index.html

As for "copying the data ..." - outside of replication, I don't know of anything
that will do that, but you can link the tables in the "other" mdb and, using
update and append queries, add and modify records as needed.
 
Is there any tool which can compare two Access databases, report the
differences between them and copy the data for matching fields between the
two if one is empty?
Click to expand...

Are you talking about two Access *databases* - mdb files containing
multiple Tables, Forms, Modules, etc. - or about two *tables*?

If the former, Total Access Detective from http://www.fmsinc.com
should do the former. If the latter, an Update query would work -
perhaps you could post a bit more description of the tables (Primary
Key, fieldnames, nature of the comparison) as well as considering
whether having the same data in two different tables might be a Very
Bad Idea in the first place (for just this reason!).
 
Can replication be configured to only copy the matching fields?
Click to expand...

I really know nothing about replication - I would recommend using queries to
update or append records. See, also, John Vinson's reply to your post, as he
brings up an important consideration (as always <g>).
 
I am talking about two separate databases ( mdb files ). My scenario is that
as I enhance my product and change access database structure, I want to be
able to offer upgrades to my existing clients and transfer their data to the
new database structure seamlessly. So I need to be able to identify
differences in their existing database and my new database and copy only the
common fields.
 
I am talking about two separate databases ( mdb files ). My scenario is that
as I enhance my product and change access database structure, I want to be
able to offer upgrades to my existing clients and transfer their data to the
new database structure seamlessly. So I need to be able to identify
differences in their existing database and my new database and copy only the
common fields.
Click to expand...

Thanks for the explanantion!

This is a complicated and difficult task at the best of times.
Automagically identifying what fields should be copied and what should
not, and what values should be assigned to newly created fields, is
NOT an easily automated process and would require human intelligence;
and often constructing the queries to perform these operations is
difficult.

For starters, you should CERTAINLY split your database: use the
database splitter wizard to create a "backend" with just the tables,
and a "frontend" with everything else, if you haven't already done so.
With this design you can change Forms, Queries, Reports etc. freely,
since giving the user a new frontend links to their existing data
without any major complications.

If you do need to change the structure of your tables, think
carefully. Ideally the table structures should be pretty much cast in
concrete by the time a database is in production. Changing field
definitions would need to be done with great care, since sometimes a
new field must be left NULL, other times it cannot be left NULL, and
so on. I cannot think of any *wholly general* automated solution. You
would need to use a tool like Total Access Detective to *identify* the
differences, and then write custom queries to update them.
 
Back
Top