Database synchronisation

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.
Rick DangerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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
Gustav BrockCIOCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Rick DangerAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.