Transfer of data between databases

[kalmed2018]

Hi,


I have four identical and quite complex databases with the exact same structure but different data. Each one of them contains three main tables and multiple queries and relationships. Each table has a primary key (autonumber) that connects the tables between them.
I am afraid that the structure of the database has now become obsolete and I am sure I could do with a better designed database from scratch, based obviously on the same table fields.
Is there any way to "merge" the data included in these four databases without creating any conflicts with the primary keys (there obviously many entries in the different databases with the same primary key)? If I would just do that using a query it would attribute random primary keys in each table and I am afraid of loosing the relationships of data e.g. from Table A with Table B.
I have tried to do it using excel as intermediary (not avoid conflicts) and then manually assign primary keys accordingly. Then I would revert back to the single table on Access. Although it is a lot of manual work I think it could work but the problem is that when I extract to the excel it will not include the attached files in some of the fields!
Any ideas if this is something that can be done just with access without the excel or how to include the attachments to excel? Any other ideas how to merge the 4 databases in one (with improved structure?)
Thank you very much in advance.

 

[kalmed2018]

With your identical tables in your four different databases, what you do is you add an extra field to each table. Use an update query to populate that field with a number to identify that particular set of table data. Put a different number in each of your databases. I would suggest 1,2,3,4 to identify each of the four originating databases. Now you have a tables that you can append together to make one complete table with all the data in one place.

Thank you for your reply, good idea it may actually work. My question is still how you maintain the relationships with the other tables in tha database. For example Customer with ID 1 in first database has Order numbers with ID 6, 15 and 56 in the same database and invoices with ID 89, 45 and 98. Currently certain querries and reports can find orders and invoices per customer. How can this continuous relationship going to be disturbed with your solution?
Thanks again,

 

[Cronk]

I've done similar data amalgamation before as well as importing data into Access from other relational databases.

If any of your tables are related and you indicate this, you need to import the key fields of the original tables into a new field prefaced with Old as well as a number representing the particular existing db as suggested by Uncle.

You need to do the same thing for any related table, importing the existing foreign key into a new field.

You then import the 'parent' tables with all records having a new Primary Key. Then for related tables using an append query, link the old tables together on the Old FK and db number, inserting the new FK from the new parent key field.

I suggest you do this at a time when you will not be interrupted and cold sober. It's too easy to stuff it up (my experience). Also don't assume everything is OK. Check your data still matches and you don't have invoices, for example, from more than one old db against the customer from only one old db.

 

[bastanu]

Cross-posted here:

Merge databases

Hi, I have four identical and quite complex databases with the exact same structure but different data. Each one of them contains three main tables and multiple queries and relationships. Each table has a primary key (autonumber) that connects the tables between them. I am afraid that the...

www.accessforums.net

Cheers,
Vlad

 

[Cronk]

I dislike cross posters who do so without disclosure. Wastes our time.

 

[gemma-the-husky]

I would probably have the data tables in separate databases, but the queries, forms reports and so on in a core front end. Then link the front end to the appropriate data tables. That way you can release the data base to other users/systems, and you don't have multiple sets of data in a single system.

So not quite merge the databases. Just have a single amount of code, so you only have to make changes to one front end.

 

[kalmed2018]

it appears to me, judging by the posting times of the op's questions on the different forums that the op didn't like the first answer and decided to get a second opinion!

Apologies, I did not mean to offend anyone. As you can see from my activity I am not a regular user of any forums and I did not think (perhaps I should) that it is not "politically correct" to do that. I am sure you would appreciate that this is not a straight forward problem and especially with me not being an experienced developer like yourselves there is no guarantee that this would work despite a lot of work that needs to be done. I thought that checking multiple solutions I would have more chances to find one that suits my knowledge of access. Once again, apologies if anyone felt offended and thank you for all your time and sharing your experience!

 

[CJ_London]

its more about being considerate rather than being politically correct.

It is a relatively common problem. A simple web search finds plenty of examples - e.g.

access vba combining two databases at DuckDuckGo

DuckDuckGo. Privacy, Simplified.

duckduckgo.com

 

[Gasman]

Apologies, I did not mean to offend anyone. As you can see from my activity I am not a regular user of any forums and I did not think (perhaps I should) that it is not "politically correct" to do that. I am sure you would appreciate that this is not a straight forward problem and especially with me not being an experienced developer like yourselves there is no guarantee that this would work despite a lot of work that needs to be done. I thought that checking multiple solutions I would have more chances to find one that suits my knowledge of access. Once again, apologies if anyone felt offended and thank you for all your time and sharing your experience!

It is not forbidden to crosspost, but one should mention it, as helpers in other forums then do not repeat what has already been offered.?
There is a finite amount of help on forums, and it should not be wasted being duplicated. The helpers give their time and expertise freely after all?