Solved

Merge two Access databases with some quirks

Posted on 2014-07-22
5
608 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:Luke Chung
Luke Chung 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:Luke Chung
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

810 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