[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4410
  • Last Modified:

merge mdb files - is it easily possible ?

The situation : I'm working with an mdb file describing a database that is rather complicated (multiple tables and references between the tables). At some point in time, a branch happened - ie. two different persons added data to separate copies of the same mdb file. Now, those two versions need to be merged into one mdb file containing all data from both (with no duplicates of course, and no clashing row id's etc.).

The question : can that be done easily, and how ?

I'm sure I don't need to mention that data loss is not an option, so it needs to be a solid procedure ;)
And the size of the database is such that it would be way too much work to copy the data manually.

I will be unavailable for comments in the next few hours (my apologies for that), but will be happy to provide more information if needed when I'm back on-line.
0
Infinity08
Asked:
Infinity08
  • 7
  • 6
1 Solution
 
Kelvin SparksCommented:
Not easily!!

Depends on the way the database is setup, if you're running autonumber primary keys then the complexity get more & more as you're unlikely to be able to reuse them, and there goes all data integrity.

I have built routines to do this, but if you didn't plan for it in the first place, then you're likely to find every pothole that exists on the road.

May pay if you tell me a bit more about the data types for the primary keys & then we can look from there.

Kelvin
0
 
Infinity08Author Commented:
>> but if you didn't plan for it in the first place, then you're likely to find every pothole that exists on the road.

There is no merge case like this foreseen in the design. The current situation shouldn't have happened, but it did.


>> May pay if you tell me a bit more about the data types for the primary keys & then we can look from there.

I think I may be lucky there : I took a look at the tables, and none of them uses an auto-incremented primary id. All primary keys consist of data that is unique between the two versions of the database (they mainly consist of one or more text fields). So it seems less complicated than I first feared.

However, this is my first experience with Access (I have worked with other databases, like Oracle and MySQL), so I'm not sure how to approach this merging ...
0
 
Kelvin SparksCommented:
Ok

Doesn't sound too bad

You are able to link tables in one database to the other (File>Get External Data>Link Tables). I'd suggest linking to the database you want to copy to the database that is the target. I'd use the larger as the target.

You then have to determine the hierachy of data. Sart with any lookup tables and ensure that all data from the database to be copied exists in the traget database (Take care not to duplicate data). A well designed append query in each case should take care of that. Thenb use append queries work from the highest level tables down to the bottom and append the data - is there a danger of duplicate data? Providing the structure is thesame, it should work well.

I also recommend leaving warnings turned on here and working manually rather than than writing a script as you can watch what is happening and cancel any append if the messages aren't what you are happy with.

As a precaution, take a copy of all databases that hold the data bfore you start, and ensure that users cannot add/update/delete until you finish.


Kelvin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Infinity08Author Commented:
>> is there a danger of duplicate data?

Except for the data that was in the original database, the newly added data in the two "branches" is different - ie. there are no duplicates.

The structure of the database has not been changed either - just that data has been added, modified and removed. Another thing that is good is that the modifications done in both "branches" were never done on the same data (ie. if in the first branch tuple A in table B was modified, then the second branch is guaranteed not to have made modifications to that tuple).
The more I look at it, the better I'm feeling about it ... which is good ;)


>> As a precaution, take a copy of all databases that hold the data bfore you start, and ensure that users cannot add/update/delete until you finish.

Yes, that is the first thing I did :)


>> You are able to link tables in one database to the other (File>Get External Data>Link Tables).

I assume that will import the data into the target database ? Not just create a link to the other mdb file ?
0
 
Infinity08Author Commented:
I will have to try what you suggested tomorrow. It's 3 AM here, and I need some sleep :)
0
 
Kelvin SparksCommented:
I assume that will import the data into the target database ? Not just create a link to the other mdb file ?

This just creates the link to the data. You can then use that linked table as a source for the append query to append to the taget table. If TableA exists when you link TableA then it will inherit the name TableA_1
0
 
Infinity08Author Commented:
>> You can then use that linked table as a source for the append query to append to the taget table.

But that would basically mean that I need to manually copy all the data, which is what I wanted to avoid, especially since I have no previous Access experience. I hope I can at least use SQL commands to do the copying for me ...

So, there's no real merge feature in Access ?
0
 
Kelvin SparksCommented:
Append is a query that will bulk copy the data from one set of tables to the other.

i.e. INSERT INTO TABLEA SELECT TableA_1.* FROM TABLEA_1
0
 
Infinity08Author Commented:
What I meant with manually was that I need to do that for every table, and that I have to monitor the process, as well as create the correct queries for every table.

I've tried it out on a small part of the data, and your procedure works, so I can do it this way - it'll just take a long time. I guess there's no other way ... I'll have to reserve some time to get this done.

If you don't mind, I'll leave this open for a little while longer to see if somebody comes up with a brilliant idea. Otherwise you get all the points ;)
0
 
Kelvin SparksCommented:
You could write a VBA script for each table & then run that, but the time taken to do that will be as long as running each manually.
0
 
Infinity08Author Commented:
I guess that would be a good approach if I had to perform this procedure regularly. But I'm hoping that's not the case ;)
0
 
Infinity08Author Commented:
Seems there are no other brilliant ideas, so I'm going with your solution.

Thanks a lot for your help, Kelvin !
0
 
Kelvin SparksCommented:
Thanks & Good luck
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now