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

[Gicu]

You can try to add field to your tables to store a "database"specific key made of a concatenation of the database name\number and the autonumber.
So for PK 1 to 10 in DB1 you would have DB1_1,DB_2,.....DB_10. Once you merge the dbs into one you now have a wy to relate back to the original ids and you can run update queries to update the FKs with the new autonumber PKs.

I used to do a lot of this kind of stuff and I found out the best way to do it was to create a "script" made of "numbered" queries (a thing I came up with ). I would name my queries 001_APPEND_ImportTable1_DB1,002_APPEND_ImportTable 2_DB1, 003_UPDATE_Table2DB1_FKs,.... I have a form that had two textboxes for start \end query number and a button to run them. This way I could run the script and modify it multiple times until I got it right.

Cheers,
Vlad

[CJ_London]

cross posted here with answers

https://www.access-programmers.co.uk...abases.315861/

[Gicu]

Thanks Ajax, funny to see that my suggestions weren't that far off...

Cheers,

[CJ_London]

I do something similar, depends on the PK/FK but I just make the destination table PK a long rather than an auto number and just add a suitable value to the old PK/FK, perhaps 10000, maybe 100000 or 1000000, then 20000 for the next db etc.

Can then consolidate them afterwards

[Kalmed2018]

Hi Vlad, thank you for your idea. I have now created the new fields as suggested but I am not sure how to create the script/query to populate the them with DB1_1 etc. I would be grateful if you could kindly elaborate on how to do that?many thanks!

You can try to add field to your tables to store a "database"specific key made of a concatenation of the database name\number and the autonumber.
So for PK 1 to 10 in DB1 you would have DB1_1,DB_2,.....DB_10. Once you merge the dbs into one you now have a wy to relate back to the original ids and you can run update queries to update the FKs with the new autonumber PKs.

I used to do a lot of this kind of stuff and I found out the best way to do it was to create a "script" made of "numbered" queries (a thing I came up with ). I would name my queries 001_APPEND_ImportTable1_DB1,002_APPEND_ImportTable 2_DB1, 003_UPDATE_Table2DB1_FKs,.... I have a form that had two textboxes for start \end query number and a button to run them. This way I could run the script and modify it multiple times until I got it right.

Cheers,
Vlad

[Gicu]

Start with a new Access file in which you import the updated empty tables with the new field. These will be your final merged tables.

Now in the same file link the tables from the original 4 databases, using some sort of naming convention to identify them (ie DB1_tblCustomer,DB1_tblOrder...). Now start building your queries "script". Usually I start with a bunch of delete queries to empty the fianl tables in case I need to run the script multiple times. Next you create an append query to add the records from the first database main table (the one with the primary key). What you want to do is to ensure you are not adding the autonumber PK into the empty autonumber PK in the target table. Instead you concatenated with the db prefix and add it to the new field you created (DB1_1,DB1_2,... ).

You then append the other related tables. Once you added all the data for all dbs you need to run up a series of update queries where you join the final PK table to a "related" table (lets say tblCustomer to tblOrder) with the join being between the new field (DB1_PK----DB1FK). In the update query you update the original FK field in tblOrders with the new PK autonumber from tblCustomer.

And finally after you ensure everything looks good you delete the links, compact the db and you're left with the merged data.

I am including a small db with the form I use to run the queries. Just make sure you name the queries 001_Append_xx,002_Delete_tbl, etc. The first three numeric characters will set the order in which the queries are being run.

Cheers,
Vlad

[Kalmed2018]

Thanks a million, I will try to fix it and update for the results.

[Kalmed2018]

Hi Vlad,

thank you for the very detailed guidance. I have now created the new access file with the three main tables (Customers, orders and invoices). Each one of them have 4 extra field (DB_1Id, DB_2Id etc). The same file also contains linked tables to the four databases (i.e 12 linked tables in total). I have managed to append the 4 linked tables "Customers" to the new single tblCustomers with the 4 additional fields. When i am trying however to do the same for the tblOrders i get an error message "An INSERT INTO query cannot contain a multi-valued field". I assume that refers to two attachment fields in my tblOrders. Is there any way around it?

Can you also please explain the update query design? How do you choose the correct PK from the tblCustomers to replace the existing FK into tblOrders. Sorry if the answer is obvious but i have never used an update query before.

Thanks again

Kalmed2018

Start with a new Access file in which you import the updated empty tables with the new field. These will be your final merged tables.

Now in the same file link the tables from the original 4 databases, using some sort of naming convention to identify them (ie DB1_tblCustomer,DB1_tblOrder...). Now start building your queries "script". Usually I start with a bunch of delete queries to empty the fianl tables in case I need to run the script multiple times. Next you create an append query to add the records from the first database main table (the one with the primary key). What you want to do is to ensure you are not adding the autonumber PK into the empty autonumber PK in the target table. Instead you concatenated with the db prefix and add it to the new field you created (DB1_1,DB1_2,... ).

You then append the other related tables. Once you added all the data for all dbs you need to run up a series of update queries where you join the final PK table to a "related" table (lets say tblCustomer to tblOrder) with the join being between the new field (DB1_PK----DB1FK). In the update query you update the original FK field in tblOrders with the new PK autonumber from tblCustomer.

And finally after you ensure everything looks good you delete the links, compact the db and you're left with the merged data.

I am including a small db with the form I use to run the queries. Just make sure you name the queries 001_Append_xx,002_Delete_tbl, etc. The first three numeric characters will set the order in which the queries are being run.

Cheers,
Vlad

[CJ_London]

Each one of them have 4 extra field

I could be wrong, but think vlad meant one extra field

existingID-db1......NewID in new db
1.........................db1-1
2.........................db1-2
3.........................db1-3
existingID-db2
1.........................db2-1
2.........................db2-2
3.........................db2-3
existingID-db3
1.........................db3-1
2.........................db3-3
3.........................db3-3

[Gicu]

Yes Ajax and Kalmed2018, I was suggesting just one field, but slighly different then what you suggest Ajax.

Existing DBs:
tblCustomer
CustomerID(PK-AutoNumber) 1,2,3
tblOrders
OrderID(PK-AutoNumber)
CustomerIDFK(FK-Number Long)1,2,3
tblInvoices
InvoiceID(PK-AutoNumber)1,2,3
CustomerIDFK(FK-Number Long)

New db:
tblCustomer
CustomerID(PK-AutoNumber)1,2,3...........1000,1001,1002
OriginCustID(Text, indexed - new field)DB1-1,DB1-2,DB1-3...........Db2-1,DB2-2,DB2-3 -populated by append query
tblOrders
OrderID(PK-AutoNumber)
CustomerIDFK(FK-Number Long)
OriginCustIDFK(Text, indexed - new field)DB1-1,DB1-2,DB1-3 -populated by append query
tblInvoices
InvoiceID(PK-AutoNumber)
CustomerIDFK(FK-Number Long)
OriginCustIDFK(Text, indexed - new field)DB1-1,DB1-2,DB1-3 -populated by append query

I proposed to leave the PK field in tblCustomers as autonumber, preserve the original values in the new field, add all four individual tables then once the other tables (orders and invoices) have been loaded with their individual datasets update the original CustomerIDFK in those (long number) to the new PKs autonumbers from tblCustomer. Once that is done the new fields (one in each table) are no longer needed and can be removed leaving the merged db similar in structure with the old one(s).

Regarding the update query it should look something like this:

UPDATE tblInvoice INNER JOIN tblCustomer ON tblInvoice.OriginCustIDFK= tblCustomer.OriginCustIDFK SET tblInvoice.CustomerIDFK = [tblCustomer]![CustomerID];

As for the error on the attachment fields I am not aware of an easy work around. I do not use them as they always seem to cause grief, I always use a table to store paths to the files. I am sure there is a way to programmatically do it (download all them into a temporary folder and right the path into a table, remove the attachment fields and run the tblOrders append queries, add the fields back then finally loop through the table and re-add the attachments to the proper records in the merged table).


EDIT: I guess you can copy the attachments from one table to another using VBA code similar to this:
https://superuser.com/questions/1565...ther-using-vba
Cheers,
Vlad

[Kalmed2018]

Thank you both, very clear and detailed info. Sorry i didnt come back earlier but i was on call the last two days.
The reason i did four fields is that (as I dont do SQL apart from basic things) i did not know how to add a diferrent pre-fix to the new fields e.g DB_1-1, DB_1-2 etc if they are from the first database and DB_2-1, DB_2-2 etc if they are coming from the second database and so on. Therefore, i created four new fields named DB_1ID etc and populated themn with the relevant append querries. I know understand that this was wrong and not what you suggested but i still cannot add DB_1- as a prefix

Thanks again,

Kalmed2018

[Gicu]

To concatenate the prefix you use "DB1_" & [CustID]. So in the Append queries for the PK table (Customers) you do not append CustID to CustID (as you want to leave the new empty table to assign autonumbers to all the records from the four dbs) but you append OrigID: "DB1_" & [CustID] to the new OriginCustID short text field.

Cheers,
Vlad

[Kalmed2018]

Hi both,

I have now managed to append two of the tables with the new field "Origin_ID" and the queries worked seamlessly! Thank you. However, when I tried to append the third table to merge the the third table from the four original databases I hit a brick wall which I am afraid there might be no solution to. The size of the new database exceeded the limit of 2GB before I could append the third table data from the fourth database. The four original databases have a very large size. The reason for that I believe (according to my limited knowledge) is that the design of the original database was wrong with almost every number field being a long integer and many test fields being long field. I have now deleted many fields that I don't use and changed the properties of the remaining fields to reduce the size but still even after doing everything from scratch and appending the existing data to the new tables with the updated properties it would reach the 2GB before I could add the last table...So close. I am not sure if you think there could be a solution to that and perhaps if I want to keep the existing data (which I do) in a single database I should have them in four separate databases and use a new one with linked tables as a tool to be able to use them opening one file. I believe this is slightly different to "splitting" the databases?

Many thanks,

Kalmed2018

[CJ_London]

hit a brick wall which I am afraid there might be no solution to. The size of the new database exceeded the limit of 2GB before I could append the third table data from the fourth database.

long datatype takes 4 bytes, text takes 2 bytes plus 2 bytes per character.

so the number 12345 takes 4 bytes

the text db1_123435 takes 22 bytes - 5.5 time bigger.

And if indexed, you can double these numbers

You could adopt my suggestion per post #5, using a long by adding say 1,000,000 to your PK for 1st db, 2000000 to 2nd db etc

1012345 will still only take 4 bytes

make sure the number you add is larger than your largest PK. with 4 db's the maximum would be 100,000,000, so your maximum PK value would be 99,999,999.

It is good practice to limit text field length (and number sizes) to the maximum required as a validation measure, but the reality is the value will only take up as much space as actually used i.e. a text field with a max length of 10 but populated with 'abcd' will only take up 10 bytes, not 22.

other common number sizes

byte 0-256, no negative, takes 1 byte
integer -32,768 to +32,767 takes 2 bytes

for other data sizes see this link https://support.microsoft.com/en-us/...2-36b93f966a33

no need to change this in your source data, just your destination tables. Note if you are approaching the 2Gb limit, you will probably get an error so better to start again with empty tables.

Field sizes should be specified in your documentation (which probably doesn't exist). So in your source data, just sort on the field and check maximum and minimum values - or write a simple query

SELECT max(fld1) as maxfld1, min(fld1) as minfld1, max(fld2) as maxfld2, min(fld2) as minfld2...etc
FROM myTable

for text max(len(fld2)), no need to chack for a minimum


If you change a field to a smaller size you will get an error on import