Background Sync of Data

[mikebrewer]

Hi all,

I apologize if this is in the wrong section... I am creating an application that is using Access as the front-end for data entry, some manipulation, etc. In the example, my guys will get a ticket and then stamp that ticket with times of when things are done. The way I'm doing it now is that I have a second Access application that is working as the synchronizer. It just sits in the background and syncs the data.

The issue I'm having is that the guys are on mobile devices so if/when they lose connection to the database (they are constantly moving in trucks), Access doesn't do a great job of reconnecting to the tables, even if you drop the tables and re-add them. I've attempted to close the sync app from the main app and then re-open it but this just results in the sync app being in the foreground and I need something that sits in the background and is hidden.

Does anyone have a good idea I can use to do this or what the best practice would be?

Thanks!

 

[CJ_London]

Suspect you will need to provide more background for a proper answer - for starters what version of access, what is the back end, where is the back end how are they connecting, what is the communication medium etc.

Does it need to be a separate sync app? why not just have it running in the background of your front end - perhaps on a timer event associated with a hidden form- it can check the table is linked and if not attempt to refresh the link.

 

[MarkK]

I don't understand the context of this problem. Where is "the background" as opposed to "the foreground?" How do remote devices connect to Access at all? Access doesn't work over http:// AFAIK. Are you running Access front ends on hand held devices? What type of data are they producing?

 

[MarkK]

CJ, you keep scooping me. I write too slow . . .

 

[CJ_London]

Mark - the floor is yours, pulled an all nighter last night for my client and now desparately need some shuteye

 

[mikebrewer]

Thanks for your replies! I apologize for the lack of info. I'm using Access 2010 and the users will be using tablets with 4g LTE connection

What I'm doing is pulling the info via this background app from the SQL database and keeping it in an Access Database on the local machine so that it is faster for the users to work on the data. Then I push any updates back to the SQL database. I'm using Access 2010 for all of this.

The reason I have so many different files...

1. I have the client interface app because I don't want any interruption for the users. If I have a timer event in the main application, it causes delays while the timer events run. Even if it only takes a few seconds, that is a few seconds I don't want to have them waiting.

2. I keep the data in an Access database as to not have sharing issues while the files are writing to the database, it doesn't cause sharing issues (I had a problem where if I only had the client app and the sync app, the databases would error because of improper sharing).

3. The background sync app is just there to mitigate data between SQL and the local Access tables so that they don't have to pull data constantly from the SQL database.

I hope that makes sense and its possible that my design is completely ridiculous so I'm open to any and all suggestions, I figured this was the easiest design, however.

Thanks again! Please let me know if more details are needed, I can be confusing at times.

 

[mikebrewer]

Also,

I'm bringing down the data to that "temporary" database because if the trucks go out of signal range and lose their connectivity, I still need them to be able to work on their tickets. After connection is re-established, it can move on with re-synchronizing the data.

Thanks again!

 

[MarkK]

Mike, sorry, but what I'm trying to say is I don't understand the problem, but I would not recommend Access for an application with any wireless connection. Access is not a good client/server application. It needs a constantly open connection to the data, and if that is ever lost, as you've observed, it's a headache. It basically crashes Access and you need to restart the app. I've even had headaches with Access networked on a wireless LAN.

If I needed to connect handheld devices over 4g to a datasource, I would just make a website, and then you have a choice of database products that would serve equally well, or better.

Those are my thoughts on this. Not really my area of expertise.

 

[mikebrewer]

yeah, that is what I have noticed. That was why I was bringing the data locally. The reason for this is I'm only really any good at programming Access as the front end. I'm not a full-fledged programming genius. I've been researching syncing data between SQL servers and possibly replicating the SQL servers down to the client level, that would also solve the trick. Haven't figured out the feasibility of that one yet.