Bring data together from several databases

[rmunteanu]

Hi,
I have 4 identical databases in different server locations (long distance, slow connection).
I want to unify data of all 4 databases in another database. I tried a single database with several access points but it doesn't work (very slow) due to the small bandwidth of the connection, so I split the database in 4 locations.
Now I need data all together. I need your help. Maybe someone has some experience in this matter.
Thank you.

 

[Galaxiom]

Search the forum for Replication for how it should have been done.

However since you didn't set up the system this way you are going to need to merge them manually. You need to be able to bring the data together and identify which records are common.

Do you have a backup of the database immediately before it was distributed? This would help a lot.

The biggest problem wil be dealing with keys which are going to have to be reassigned since the different databases have probably used the same key values.

Post an image of the relationships window so we can get an idea of the structure and any key fields. Point out any autonumbered key fields.

 

[dfenton]

Using Replication for multiple sites is quite complex (even though basic direct replication is quite simple). If at all possible, I'd try to avoid it. The easiest solution for multiple locations where there's an always-on Internet connection is to host the app at one location on Windows Terminal Server, and access it across a VPN. That can work extremely well even across low-bandwidth connections.

Where there's no always-on Internet, indirect replication is an alternative, but it's quite complex to set up and requires regular maintenance to make sure it keeps running reliably.

The Jet Replication Wiki brings together references to all the resources you need to get started with replication:

http://dfenton.com/DFA/Replication/