I develop an application that saves records in MS Access DB.
Each user has it own DB copy on his local machine, and there is a central DB that reflects all the copies from everyone. The central DB is located in a URL that everybody can access.

I therefore distribute the central DB by creating local copies that are links to the central tables. This means that the local DBs are synchronized with the central since they are links to it.
However, this solution disable the option to work offline- if a user is not connected to the URL, he cannot save anything on the local DB which is a link to the central .

I therefore have another option: to distribute the central DB by copying deep copy, and locate a copy (not link) on each machine. This enables to work offline since each user has a full copy of DB on hos machine.
However, no synchronization among the copies and the central DB.

Is there any solution that enables both offline work and full synchronization? Any Ideas?

  • You could make it a 2 step process. Start by developing it as a standalone local app, after each insert/update you could synchronise the local version with a central db. Another option could be to use access as a front end to a SQL express db, and use replication or ssis to synch/merge db's. – Sir Swears-a-lot May 16 '16 at 9:02
  • @Peter, Thanks. As I understand, using front-ends and one backend (central) is the first option I suggest. While, your first suggestion is my second. Right? – user3165438 May 16 '16 at 9:09
  • Correct. To be able to wotk offline to need to have a self sufficient db. You cant really avoid that. There are various methods and tools for synchronising the data varying in sophistication and cost. The cheapest and most basic options might the user to turn something on/off. The more yoj want to automate this the harder and more expensive it gets – Sir Swears-a-lot May 16 '16 at 9:18
  • @Peter, Thanks for the clarification. I search for a solution that enables to work offline as well as auto-sync. Is there any? – user3165438 May 16 '16 at 9:20
  • Not that im aware of with access. Even with SQL server youd need to understand the business rules, and the schema would need to support the logic. If one user gets disconnected and updates a record and at the same time another user updated central db... which version is correct when you try and merge? – Sir Swears-a-lot May 16 '16 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.