Solved

Merge two Access databases with some quirks

Posted on 2014-07-22
5
597 Views
Last Modified: 2014-07-25
Hello, so I have this customer who made quite a mess out of his Access database.
Two versions of the same database have been worked on, so that one version has more records than the other, but the other version has many corrections on common records.

So there's a table with records for july, and the other table with no july records but more up to date records up to june.

Moreover, tables have usually correlated records in other tables.

So, if I want to keep the up to date records and add the new records, how would I proceed? Keep in mind I'm a TOTAL Access newbie.

Thanks.
0
Comment
Question by:Daniele Brunengo
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 300 total points
ID: 40213141
Well, it's kind of complicated, but you may be able to copy and paste the records if you proceed according to the table relationship hierarchy. Let's say you have this kind of schema

Products --< Order Details >-- Orders >-- Customers

... where "--<" is one-to-many and ">--" is many-to-one.

Start with the database that has the June data in it, and add the more recent data from the other (July) database.

Start with the Tables that are on the "one" side of the relationships with no other tables related. that woould be Products and Customers in my example.

Make a backup of your June database.

Make note of the record IDs from any new Product records in the July database. Copy the first one added since June, and paste it in the June database. Did it get added with the same record ID as the record in the July database? If so that's good news. Now copy and paste the rest of the Product records and verify the ID field values match your July database.

Do the same for the Customers table, or any other table on the "one" side of a relationship with no other related tables.

Next, move in one hierarchical level and do the same with the "Orders" table in my example. Lastly, do the table that has multiple "many" relationship links, like "Order Details" in my example. Keeping the key field values in the June database identical to the July database is the key to success.

If you can't get the key values right because of autonumber data type problems in the key fields, you may want to temporarily change the data type from autonumber to number, and remove the indexes on that field. then you should be albe to type the key values so that they are identical to your July data. Remember to change the field type back to autonumber and recreate the index when you are done.

Make backups before each step!
0
 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 200 total points
ID: 40218090
This could be a very challenging problem even for experts. Hopefully the database structure isn't too complicated to identify what's different.

When combining two databases, the first issue is whether the database architecture changed. Make sure you understand if any table structures were modified (fields added, deleted, or renamed) and changes to properties. If the structures changed, you'll need to figure out how to reconcile that before trying to combine the data.

Second, you should check if other design elements changed: queries, forms, reports, macros, modules, etc. You'll need to identify the modifications to reconcile which objects to keep or modify.

Finally, you'll want to combine the data into one master database.

To compare two Access databases for detailed differences, our commercial product, Total Access Detective, is commonly used. It will tell you exactly what's different between the two databases at the table, field, control, object, line of code level. It will also tell you what's different between the data in your tables. It has a feature to combine the data from the tables if you can identify one being older than the other, but it can't reconcile differences if changes were made simultaneously.

If you want to have multiple users, consider using a split database design so multiple people can share the same data. We wrote an article discussing that: Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability

Since you're a beginner, you may find our resources in our Microsoft Access Developer Help Center to be useful.

My paper on Taking Over (Inheriting) Legacy Microsoft Access Database Applications may be particularly relevant before you get too technical.

Hope this helps.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40218233
Luke, great additions to this thread! I look forward to reading your "Taking Over..." paper in depth - that's a good part of how I make my living nowadays!
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 40218288
Glad you like it. Look forward to any feedback you may have on my paper.
0
 

Author Comment

by:Daniele Brunengo
ID: 40218697
I more or less managed to merge the databases (no modifications were made to the database structure, all data were inserted using a form which opens with the database).

Thanks for both your inputs. I'll share points. Little bit more for pdebaets who gave me the actual solution.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

14 Experts available now in Live!

Get 1:1 Help Now