Merge two Access databases with some quirks

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.
Daniele BrunengoIT Consultant, Web DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdebaetsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
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
pdebaetsCommented:
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
Luke ChungPresidentCommented:
Glad you like it. Look forward to any feedback you may have on my paper.
0
Daniele BrunengoIT Consultant, Web DesignerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.