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.

 

[Uncle Gizmo]

I have four identical and quite complex databases with the exact same structure but different data.

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.

 

[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,

 

[Uncle Gizmo]

Well I don't know if it would work, but it would be worth a try. Add the 1,2,3,4 to all of the other tables in the four databases, then adjust your queries to filter on the the 1,2,3,4 plus the other identifying number. Don't forget, before you start making extensive changes, make some backup copies of your database first! I usually make one special master copy called "MASTER" and I never touch it. I leave it somewhere right out the way, where I can't get at it easily.

 

[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.

 

[Uncle Gizmo]

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

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!

 

[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

 

[Pat Hartman]

Cronk has given you the solution I use when I have to merge tables. It probably won't take even a half hour to implement. But, why are they separate now? If they are separate because the break has meaning such as data for client1 in db1, data for client2 in db2, etc., then you will need to incorporate that information as you build the new database and you should have a new table as well to define all the "types" that caused the split in the first place.

Someone else mentioned splitting the database into a FE (forms/reports/modules/macros/links to tables in the BE) and a BE (tables). Even if this isn't a multi-user application, you will find it easier to manage as two separate databases. It does pose some inconvenience if you send the "files" to other people but you can get around that fairly easily.

If you are rebuilding the app from scratch, rethink the design and normalize the tables if they are not already normalized. Also, standardize your naming conventions for objects including field names. Do NOT use special characters (except the underscore) or embedded spaces. My preferred standard is called CamelCase with each "word" capitolized. You type the name with caps when you declare it and the rest of the time, you can just type lower case and Access will cap it for you. It is an easy way to immediately recognize typos. Others prefer the_underscore. I don't like this because the "_" requires shifting and I don't like shifting when I'm typing code. So, I only use the_underscore when I want highlight something in the name.

 

[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?