• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

SQL Server; storing data in offline mode.

I have a non-WEB based Windows app that our technicians use in the field on PC tablets.  It saves entered data to an Access database.  When the tech is done working for the day, there is a sync feature that syncs/sends the Access data to a SQL database in our home office.  There are multiple issues with this syncing process that cause us grief.  Duplicate data, loss of data, etc.  

What would be nice is to have a WEB-Based app on the technician tablets, that can talk directly to the SQL server database. The problem  is that due to location, the physical environment, etc., the tablets often cannot get a WIFI connection/signal.  

What would really be nice: The technician can enter their data on the tablet and if there is no WIFI connection, the data is saved somehow - (cached, stored, ???  etc) without using a local Access database, using some SQL or other robust mechanism, until such time that there is a WIFI connection and then write the data to the home office SQL database.

Hope this make sense. Anyone have any ideas?
  • 3
  • 2
  • 2
  • +1
4 Solutions
Changing to using a Web app isn't going to solve the problem.  The problem is sync'ing.  

Your sync'ing process needs to be fixed.  It needs to recognize duplicate sync request and either reject the second and subsequent requests or delete the data from the first sync and try again.  That will also help you recognize loss of data.

Without more details it is hard to be much more specific.
Welcome to my box.
It's not ours alone by a long shot.
I've been trapped in it for quite some time.
So have myriad others.
There are no easy answers.

Syncing offline data is always a nightmare.
Who added what?
Who changed what?
Who did so first?
Who's additions/amendments should have priority?
How should the losers in that algorithm be notified that their changes haven't been propogated?

All very difficult and evil stuff to manage.
And I haven't managed.

What would really be nice: The technician can enter their data on the tablet and if there is no WIFI connection, the data is saved somehow - (cached, stored, ???  etc) without using a local Access database, using some SQL or other robust mechanism, until such time that there is a WIFI connection and then write the data to the home office SQL database.

It is not that Access isn't robust and some other technology miraculously will be, it's that the whole process is incredibly pain-staking to plan, code, test, refine and rollout.

Even when you disallow offline edits of existing data, the process doesn't get a lot simpler.
You have to build mechanisms that detect what was added by the offline user(s)
You then have to build mechanisms that add that data, one record at a time, one table at a time, that maintain referential intgerity and the integrity of the offline data.
You have to ensure that when more than one user is syncing up data that this doesn't break the process.
And then you have to build mechanisms to refresh all the data in preparation for going offline.
And all these mechanisms have to work flawlessly, without fail.

That's a very, very high bar for any non-dedicated professional development firm to hurdle.
What would really be nice: is if wishes were horses, but they aren't.

Connected apps like Access can be very powerful.
Disconnected apps like  internet apps can deal with the disconnectedness right at the time they commit the data.
But a truly disconnected multi-user app that punches up batches of data -- well that takes a professional development team, and even they drop back and punt a lot of the complexity to the user when the syncing get gnarly.

Sorry that I don't have a better vista to share with you inside the box.
HLRosenbergerAuthor Commented:
Nick67 - Thanks so much for your feedback.  I'm taking over pre-existing code that saves to Access in the field, and then does a sync process to SQL server.

Probably what would also help is to limit what data actually gets modified in the field, yes?  Keep it simple as possible.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

One way to control things is to send the tech data about the assigned tasks via email.  You can attach a spreadsheet that he can import.  That minimizes what he has to type and therefore reduces errors.   Some data about the job will be entered into the database and then collected during a sync.  Rarely, basic data will need to be changed.  This should be done using a separate table to clarify what exactly is being changed.  That way the sync doesn't have to guess.  It is clear that the tech is specifically changing the client's email address or whatever.

The import process should ask the tech if he wants to clear the old data.  This allows him to start fresh each day  with only the current tasks.  But the app should still work even if he doesn't explicitly clear old tasks.  It should track what time a sync was done and ensure that old data isn't resent.

As Nick said, it's complicated.
Probably what would also help is to limit what data actually gets modified in the field, yes?  Keep it simple as possible.


Two items with two choices -- 4 possibilities
Three items with two choices -- 8 possibilities
Four items with two choices -- 16 possibilities

Two items, three choices -- 9 possibilities
Three items with three choices -- 27 possibilities
Four items with three choices -- 81 possibilities

To get any kind of scenario that's codeable and workable you have to be absolutely ruthless in minimizing the possible outcomes.  If you do not permit edits to pre-existing data when offline, the problem gets simpler as all that has to be dealt with is records added when offline.  The next step to simplify is if the offline user HAS to be given a unique datum  generated by the main system online, and the main system correlates that generated datum with the user.

Then your syncing system, when it goes to do its magic, can ask the user for that unique generated datum, find the primary key value in the user's offline (but presently accessible) Access database, and begin appending each offline record in each offline table that has that PK value to the main data store (and error checking that the record does not already exist as it goes)  After the sync is complete, the system then rubs out the entire offline data store and replaces it with a copy of the master data store.

Workable, but still extremely complex, and it has a two step process involved.  The user HAS to be given that pregenerated unique datum, and the sync system has to use it.  Getting it fully automated so that user screwups don't factor in gets harder yet.
I've done three intermittently connected (IC) apps that would fall under the category of "What would really be nice". To address your question, I used SQL Server, MySQL and XML for offline data storage in those projects.

Nick67 has graciously offered quite a bit to consider when engineering an IC app. Naturally, I disagree, with all due respect, in the number of developers required. You don't have a complex task, you have a thousand simple tasks.

On that note, SQL Server 2016 includes temporal tables and row-level security, both of which could be extremely handy for IC apps.

Good luck!
Does my description of what is involved jive with your experience in executing such a task (or successfully integrating a thousand simple tasks.)
How did you go about defining the problem?
What approaches worked?
What approaches proved to be unworkable?
What pitfalls did you encounter?
What kind of roadmap would you suggest?
Nick67, your descriptions of some of the issues involved were spot-on.

There's a number of loaded questions there!  As I'm sure you know, each project, and especially the syncing involved, is unique.

One thing that has to be determined is a method of concurrency. No method is perfect, so choices need to be made.

I believe in all cases a SOAP web service was used to act as "middleware" to manage transmissions and process the flow. Handy in the regard that it accepts .NET objects.
HLRosenbergerAuthor Commented:
Thanks for all your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now