[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Database synchronisation

Posted on 2013-12-29
Medium Priority
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 500 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 500 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 52

Accepted Solution

Gustav Brock earned 500 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 39

Assisted Solution

PatHartman earned 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

650 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