Merge databases

[Gicu]

In addition to Ajax's suggestions I would recommend you compact the new database after each db is appended; if you run the update queries for each db as you load them then you can empty the Origin_ID fields after the foreign keys have been updated.



Cheers,
Vlad

[Kalmed2018]

Hi Ajax,
thank you for your comments. The problem is due to the initial size of my original databases and not because of the new field and the new PK. My four database's size are: 1.68GB, 1.4GB, 1.63GB and 0.93GB. Changing the field properties did not make them any smaller! do you think that with your method i could fit all the data in a single database?
Thanks

[Gicu]

Have you considered the free MS SQL Server Express?

https://www.microsoft.com/en-us/down...aspx?id=101064

Cheers,
Vlad

[canyonraven]

Hi Kalmed,
It's always a good idea to have a quality assurance plan for the final outcome after a big merge operation. This can be done be pulling 10 % of your outcome records ( ID - (CInt( ID/10 ) * 10) ) and compare them with the original tables. There should be absolute match between your source and final records.

[CJ_London]

Changing the field properties did not make them any smaller! do you think that with your method i could fit all the data in a single database?

it won't until you compact the db's. If you have compacted, then I suspect something else is wrong or the changes made have minimal impact

Without know what your data is, it may well be that the stated sizes of your source tables does mean you cannot combine the data into one db - in which case you will need to use sql server express or similar as suggested by Gicu.

If you are happy with adding 1m/10m/whatever to the source PK/FK fields, then you can just update the PK autonumber field. Saves you a field and an operation to tidy thinks up later. You can't manually populate an autonumber field, but it can be done using sql.

[canyonraven]

A quality assurance plan will require cross walking the new primary keys with the old ones so don't lose the old ones until you've checked the data.

[ssanfu]

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

Maybe you would post an image of your relationship window?

Better would be if you could post the dBs. I realize the file sizes are too large to post here, but you could try "BOX" (realize some people will not download from external sites. Or delete all but a few records in each dB and post here.

I have a test computer that I frequently wipe and restore the OS, so I'm not worried as much about malware.

[Kalmed2018]

Hi Vlad,
I am not familiar to the SQL Server Express. What is that and how this can help with my problem? Is it not the same with having for example a database with linked tables to my four databases and control all of them through a single application? I did have a quick look of the specifications and it looks like the size limit is indeed 10GB but I would not be able to transfer my attachments. Is that correct

Many thanks,

Kalmed2018

[Gicu]

MS SQL Server Express is the free version of Microsoft's flagship relational database. You are correct that the size limit is 10Gb and that it does not have an "attachment" data type like Access does. I assume you are not using the "attachment" type as you would have had problems with the append queries (they don't work with multi-value fields like the attachments). In SQL Server you can store files as "BLOB" (see this for more info:https://www.microsoft.com/en-us/rese...x%3Fid%3D64525).
In MS SQL Server Express you would be able to merge the four db's into one as per your original goal. But if you want to keep it in Access why not just move the attachements (I assume you have them embedded as OLE objects) to the file server in a organized folder structure using some sort of naming convention for folders (CustID) and files (Invoice#.PDF) in which case you wouldn't even need to store the path in the db (which is the easiest way to deal with external files).

Cheers,
Vlad