gladmins
asked on
Merging Two Access Databases To Create a Single Up To Date Accurate Database
Currently there is a Microsoft Access Database containing thousands of customer records, details such as emails, address, phone numbers and more. There are many linked tables, and the data is quite messy but the system has been working.
We have since discovered there are actually two databases, "Database" and "Copy of Database". It turns out that people have been updating the "Copy of Database" instead of the original that the majority were using. The records that have been changed on "Copy of Database" include 5 months of daily email updating and detail changing. This involved mainly removing records and changing email addresses.
Is there a way to somehow compare the "Database" and "Copy of Database" so that the databases get merged to create an accurate database instead of one where "Database" is 5 months old, and "Copy of Database" is semi up to date.
We are using Microsoft Access 2003, there are many tables, queries, macros and forms in this database and we need to get the most accurate up to date version by merging and comparing the two.
Is this possible? Any help is greatly appreciated.
We have since discovered there are actually two databases, "Database" and "Copy of Database". It turns out that people have been updating the "Copy of Database" instead of the original that the majority were using. The records that have been changed on "Copy of Database" include 5 months of daily email updating and detail changing. This involved mainly removing records and changing email addresses.
Is there a way to somehow compare the "Database" and "Copy of Database" so that the databases get merged to create an accurate database instead of one where "Database" is 5 months old, and "Copy of Database" is semi up to date.
We are using Microsoft Access 2003, there are many tables, queries, macros and forms in this database and we need to get the most accurate up to date version by merging and comparing the two.
Is this possible? Any help is greatly appreciated.
ASKER
Thanks Slamhound for the response.
There is a unique ID, a field called "Member ID" and that ID contains the same customer details. It looks like Joe Bloggs has a Member ID of 14993 in the new database, if I go back and search in the old database under Member ID I can find the same number and it matches up to the same user.
There does not appear to be any dates of when the record was changed / updated.
Is there a built in tool I can use to fix this up?
Cheers
There is a unique ID, a field called "Member ID" and that ID contains the same customer details. It looks like Joe Bloggs has a Member ID of 14993 in the new database, if I go back and search in the old database under Member ID I can find the same number and it matches up to the same user.
There does not appear to be any dates of when the record was changed / updated.
Is there a built in tool I can use to fix this up?
Cheers
Short answer: No built-in tool
With a unique identifier, and tables of the same name, it IS possible to go through two db's, and
a. Find those records not existing in tables in db A and in db B, and vice versa.
b. Check for differences when that unique identifier exists in like tables in both db's, and post those differences into a table for later review
c. Write all records from both db's into a third db, negating absolute dupes with a union query. (Problems if there are memo fields which might not match.)
You probably need a combination of 2 or 3 of above approaches to get the absolute solution. Requires some sql, and, to speed things up, maybe a little code. Beware of small sample sizes when determining that existence of a record with member ID 'x' in two db's/tables means that there are absolute (and accurate) matches in all cases.
If there are not unique IDs in some tables, there are other methods, such as calculating a CRC from record contents.
Can you post a sample db with table structure (no data necessary) to www.ee-stuff.com with a link to this question?
Your solution might be simple, or could involve some somewhat complex coding. Are you comfortable with vba? If you want to work through this and learn, it may take a bit of time , but you will learn. If you need a total solution, it may be even longer.
With a unique identifier, and tables of the same name, it IS possible to go through two db's, and
a. Find those records not existing in tables in db A and in db B, and vice versa.
b. Check for differences when that unique identifier exists in like tables in both db's, and post those differences into a table for later review
c. Write all records from both db's into a third db, negating absolute dupes with a union query. (Problems if there are memo fields which might not match.)
You probably need a combination of 2 or 3 of above approaches to get the absolute solution. Requires some sql, and, to speed things up, maybe a little code. Beware of small sample sizes when determining that existence of a record with member ID 'x' in two db's/tables means that there are absolute (and accurate) matches in all cases.
If there are not unique IDs in some tables, there are other methods, such as calculating a CRC from record contents.
Can you post a sample db with table structure (no data necessary) to www.ee-stuff.com with a link to this question?
Your solution might be simple, or could involve some somewhat complex coding. Are you comfortable with vba? If you want to work through this and learn, it may take a bit of time , but you will learn. If you need a total solution, it may be even longer.
there is no simple way of doing this since both db were being updated, you would need to know on a record by record basis which one to keep.
there may be IDs in a table in one db that may not be present (or be a different client, customer etc) in the other.
i think you'll have to enforce that only one db (the more up to date) is used from now, and then go through the painstaking process of identifying which record requires updating (or inserting/deleting) from the other.
there may be IDs in a table in one db that may not be present (or be a different client, customer etc) in the other.
i think you'll have to enforce that only one db (the more up to date) is used from now, and then go through the painstaking process of identifying which record requires updating (or inserting/deleting) from the other.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you slamhound, and everyone else for contributing. Ended up doing what Slamhound suggested and ran a few queries to cut it down but it seems to be as up to date as it's going to get.
Thank you also for the offer jerryb30, most appreciated.
Thank you also for the offer jerryb30, most appreciated.
But unless there's a date on any of the fields, you will nto be able to tell which is the most recent update. Instead, your going to have to create some rules: More notes means this is the latest record...fax filled in measn this is the latest record etc