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?
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?
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.
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.
ASKER
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.
Probably what would also help is to limit what data actually gets modified in the field, yes? Keep it simple as possible.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your help!
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.