Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Merge two Access databases with some quirks

Posted on 2014-07-22
5
Medium Priority
?
641 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 1200 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 800 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

876 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