Combine two databases with lots of tables with same name

[FoolzRailer]

Hello

First off I just want to say I'm rather new to Access and the function therein.

I have two databases that I need to combine/merge/append into one huge database. Most of tables in each database have the same name, but each database also contains tables the other database doesn't have.

What I would like is this.
1: Add the tables database 1 has to database 2.
2: Add the data from all the tables with the same name from database 1 to database 2.

Is this possible? I have added a picture of the two databases.

Any help would be much appreciated!

[orange]

Please tell us more about the 2 databases. How were they designed/developed? How do they operate now?
Why do you want to combine them?

Do you have a detailed description of the business these databases support? Do you have a data model?
Do you have any data management policies/standards in your organization?

Do you have a strategy or plan? What about the next database?

[FoolzRailer]

Will try to answer as best I can. The data is supplied from two different companies (writing my bachelor as a engineering student). I need to combine the two in order to have a complete system, which I can then analyze for overloaded sectors, water on ground level when it's an extreme rain event etc.

The two databases are both generated from the same program called Mike Urban (Made by DHI). They contain all data on a sewer system in a town, the east and west side respectively. This data is then intrepreted in the Mike Urban GUI as a model of the sewer system.
Fx. the tables called msm_nodes contain data on manholes, like elevation data, the other tables contain data on pipes, shapes, catchment areas etc.

Now in Database1 there is a list of manholes including data along with their geographic placement, this I would like added to Database 2. A bit much to copy paste all the data, so hoping there was a way too do this.

Thank you for the quick reply! Hope this answers your question. I've added a few pictures of the model.

[orange]

I am not familiar with Mike, but did see https://en.wikipedia.org/wiki/MIKE_URBAN
If the 2 databases are from 2 companies why do you want to combine them? Are you comparing 1 set of data to another?
Did someone senior tell you to do this-- with some sort of guidelines?

[FoolzRailer]

Yes, that's the program. You can draw and add data too the layout window, which will then fill that data into one .mdb file.

The databases are just supplied by the two companies, in order to give me a complete model of the sewer system I need to combine the data. I can't run simulations on the model, unless I have the full combined system to do so. Otherwise the results from the simulations will not be correct.

Didn't get any guidelines or anything, just got the data supplied and then I'm pretty much on my own. I tried doing an Import, but instead of adding the data msm_nodes from database1 to database2, I got two different tables called msm_nodes and msm_nodes1.

[ItsMe]

The problem I see is that you are going to have duplicate Key values. In other words, one database will have a unique record of 2185 and the other database will have a unique record of 2185. However, when you compare the attributes of said records, the attributes may be different. What is sometimes used as Keys are GUID's. For instance, the value 2185 can fit into a Long Integer data type. It is common to use Long Data types as Keys in Access. If you want to synchronize two or more databases, you might depend on a GUID data type for PK's.

A GUID generated by a remote database will be unique from a GUID generated from a local database (yes, I am aware of the arguments that GUID's may not always be unique).

In all likelihood, your two databases are using Long Integer and Autonumber types. You might be able to do a conversion of the keys to GUID. So, receive some new
data and run procedures against the new data that would cascade throughout, updating longs to GUID's. However, considering the complexity of the database, this seems like a daunting exercise.

[FoolzRailer]

Now, I'm not sure if I understand what your saying entirely, so sorry for my ignorance. I believe the data should not use the same IDs inside the tables, as each node/pipe/catchment is unique and needs to manually entered with their own objectid and muid. I know it is possible to add data by typing it into the database, and then it will show in the program.

So what I'm asking then, is it possible to merge just the tables with the same name, thereby adding the columns and rows of information from each table with the same name.

[ItsMe]

...is it possible to merge just the tables with the same name, thereby adding the columns and rows of information from each table with the same name.

If I take your question literally, no, you cannot add columns from two different tables together. The way I understand your question is to take five columns from one table and five columns from another table to produce a final table with 10 columns.

However, if you maintain the same number of columns and append only the rows from two tables, yes, you can do that. The covenant is that you cannot violate constraints. For instance, when you append a record to a table, there may be a constraint that limits a key field to not allow duplicates.

[orange]

We (readers) don't know the details of the MIKE system, nor your data tables. As ItsMe said, you could have duplicate IDs and that could interfere with your attempts. If each of the 2 databases represents a different set of node/pipe/catchment data, then I don't quite understand why they could not be loaded/sent to MIKE individually--but I don't know MIKE nor how the Access-based data gets processed by MIKE.
I do recommend that you make backups before proceeding.
Have you asked others in your office how they processed multiple recordsets/databases?

[FoolzRailer]

However, if you maintain the same number of columns and append only the rows from two tables, yes, you can do that.

This is what I meant, sorry if I was unclear, my english isn't what it used to be. How would I go about appending the rows?

We (readers) don't know the details of the MIKE system, nor your data tables. As ItsMe said, you could have duplicate IDs and that could interfere with your attempts. If each of the 2 databases represents a different set of node/pipe/catchment data, then I don't quite understand why they could not be loaded/sent to MIKE individually--but I don't know MIKE nor how the Access-based data gets processed by MIKE.
I do recommend that you make backups before proceeding.
Have you asked others in your office how they processed multiple recordsets/databases?

Very unstandable you don't know Mike as it's a danish system, not really widespread. I understand the whole problem with multiple instances of the same ID, but if that isn't the case and the IDs are all different then it could work? Mike can't open two different access databases, it does however allow for import/export of the data, though currently they have a bug (which is being worked on) that ruins this function, hence my predicament. An update for this could be days/weeks/months off, and during this time I can't work on the model.

Again appreciate all the help, really nice of you, and so quickly as well

[orange]

I'm not sure what structure Mike needs. And I'm not sure how important it is for you to keep the data from the 2 databases separate.
You could build a third database, and import the data from the original 2. You could add a field to identify which database a source record came from. Then process only this third database.

[ItsMe]

How would I go about appending the rows?

As orange described in post #11, it is difficult to know how and, even, which tables you would need to append.

I tried to touch on some of these issues in post #6. It is likely you will need to treat the two databases as raw data and import the data into your own application. It would probably take me longer to (completely) reverse engineer the existing application than to create my own, unique application and import the data from the two apps as raw data.