Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2007-10-03
Medium Priority
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

ID: 20011121
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

ID: 20011177
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 26

Expert Comment

ID: 20011554
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Expert Comment

ID: 20011721
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

slamhound earned 2000 total points
ID: 20018871
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

ID: 20018922
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

571 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