Merging strategy for access 2007 database

Question

I have a Windows application with access 2007 database which is deployed at 2 different locations - Sales office/Factory. Every day both the locations need to sync up the database so orders are propagated to the factory and order status/invoices/production updates are propagated to the sales office. The schema for both the db copies is identical and most tables have autonumber primary keys. Client would like to be able to ftp drop/email the accdb files across and run a program that syncs the database. I googled upon a utility for older version of access (mdb) but nothing for 2007 version. Is there anything out there that can help me achieve this or will i have to look for a homegrown solution? What would be your solution? Migrating to SQL is not an option :)

Answer 1

This is an extremely complex topic, particularly in the multi-master scenario where both dbs are being updated.

The simple answer is to eliminate the need for two copies of the database by providing access to the same database from both locations. The simplest way to do this is to host the app on Windows Terminal Server. If full-time Internet access is not possible, then other solutions, like Sharepoint, become more viable. Programming the synchronization is an exercise in real punishment, as the issues with multi-master editing are pretty severe.