Merging Two Access Databases To Create a Single Up To Date Accurate Database

Posted on 2007-10-03
Last Modified: 2013-11-29
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.
Question by:gladmins
    LVL 10

    Expert Comment

    Is there a unique ID that's used in both databases? Then there's how we get the two bits of data side by side to compaire. If a field is blank in one, you can write the data from the other.

    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

    Author Comment

    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?

    LVL 28

    Expert Comment

    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 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.
    LVL 19

    Expert Comment

    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.
    LVL 10

    Accepted Solution

    One other possibility is that you find obvious master records (eg. This person has no address therefore, the other record with the address will overwrite this one), update on of the databases and in the process, delete the old record. Then hopefully you will have a short list that someone can manually sort through.

    Create an Access database that is linked to both databases and show the linked userIDs and associated records to the user side by side. then a smple button can allow them to update one record from the other and delete the first. This would allso allow them to manually merge the records if there's no clear "winner".

    Sounds labour intensive but hopefully the automated process should cut down the manual effort required.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now