Database synchronisation

Posted on 2013-12-29
Last Modified: 2013-12-30
I have a database with an Access front end and SQL Server back end, hosted on the web. It all works fine, but sometimes, the user will not have a good internet connection, and therefore cannot use the database.

Is there a tool, or method, by which he can use the database even when connected? So, if he has a good connection he uses it normally, then when he does not have a connection, the database resorts to a local version back end, and then, when re-connected, the database will somehow synchronise and update all records.

As I see it, the main problem is if one user starts creating records whilst in local mode, and at the same time another user adds a record to the web-hosted back end, then the synchronisation needs to account for these records.
Question by:rick_danger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 39744617
I'm not sure how you'd determine whether you have a 'good' connection or not, so it's hard to say exactly what sort of methodology you'd use for this.

The only way I really see something like this working is if you always work on a local copy, and then provide a "sync" button where there user could upload their changes to the live database. You'd then execute the various UPDATE/INSERT/DELETE statements to manage the data. You could even do this periodically "behind the scenes". Essentially you could create a global connection, and check the state of that connection before issuing those statements:

Public con As ADODB.Connection

Function CheckConState() As Boolean
  If con.State = conStateOpen then
     CheckConState= True
    CheckConState = False
  End If
End Function

That's very simplistic, and you'd need to flesh it out, but essentially that what you'd do. So before issuing the statements:

If CheckConState Then
  con.Execute "SQL Here"
End If

You could either (a) store the SQL statements in a table, and have them ready to process when the connection becomes live or (b) add "state" columns to each table to indicate which rows need tto be handled.

In (b) your "state" column would dictate what needs to happen - for example, I could query the Customer table for all rows with a value of "UPDATE" in the column, and then run an UPDATE query to modify the data in the live Customer table with the data contained in the local Customer table for that row.

Of course, you need to manage conflicts, and that's where the issue arises. If UserA modifies RecordX, and then UserB comes along and does so at the same time, who wins? You'd have to develop methods to manage that, and I'm not sure how you'd handle that since each situation is different. I created a sync system for a client, and it was very, very complex (due to their requirements), but I've also created them where the "last one in wins", which is much simpler.

Also, this really won't work with linked tables, since you have no control over the connection of those tables in any real sense (i.e. you don't open and close them, you just work with them). You'd almost certainly have to move to an unbound mode, or make extensive use of temporary tables to do something like this. In other words - a LOT of work.
Also, SQL Server supports replication, which does much of what these  processes do. Replication can be tricky to setup and manage, however, so do some reading before you decide to go that route.

Assisted Solution

QuinnDex earned 125 total points
ID: 39744696
you will run into problems if data from different users is relational, ie if a change one user makes would negate a change another users has made, and this could cascade down many changes and across many users
LVL 50

Accepted Solution

Gustav Brock earned 125 total points
ID: 39744796
The only reliable method, I guess, would be to install a local SQL Server Express, link to that, and set it up as a replication slave to the hosted SQL Server which, further, should be set up as the replication master. This, of course, requires adjustment of the hosted server which may not be possible.

LVL 37

Assisted Solution

PatHartman earned 125 total points
ID: 39745299
Using Citrix or Terminal Services, you still need an internet connection but the footprint is smaller so you might get by with a poorer connection.

Converting the BE to SharePoint would give you the option of working offline but since SharePoint lists are not really relational, I wouldn't use them except for extremely simple applications where I was sure I could implement RI completely with my own code.  And, you still have the problem of reconciliation when users update the same record.

I haven't used replication but Gustav's suggestion has merit.  It really depends on how reliant your app is on existing data and whether multiple users are likely to be updating the same data between synchronizations.  The reconciliation could be quite complicated.

If the external users are primarily doing data collection, I would go with Scott's suggestion to always work off-line and then upload.

Author Closing Comment

ID: 39745741
I've decided to share the points - I hope that's OK with you all. I'm really grateful for the time you have all taken, and for the clarity of your solutions.

Happy new year to you all!

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how the fundamental information of how to create a table.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question