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
LVL 84

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 49

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 35

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

776 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