Solved

Database synchronisation

Posted on 2013-12-29
5
250 Views
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.
0
Comment
Question by:rick_danger
5 Comments
 
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
  Else
    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.
0
 
LVL 9

Assisted Solution

by:QuinnDex
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
0
 
LVL 49

Accepted Solution

by:
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.

/gustav
0
 
LVL 34

Assisted Solution

by:PatHartman
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.
0
 

Author Closing Comment

by:rick_danger
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!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now