Offline front end database

[JDCD201]

Hi Guys,

I have created a database to be used for lift maintenance audits. The idea is a central database is used to produce reports using mail merge.

This all works very well.

However, since we have purchased tablets we would like to use them to carry out the surveys rather than use paper forms and enter them in back at HQ.

The issue here is they will often be in lift shafts/pits & are not guaranteed to be able to access the internet on client sites.

The database and forms works fine on the individual tablets but the issue I have is how do I keep all of the data up to date & in sync?

In order to ensure the primary keys are unique I have set the system time & tablet number as a string to be the primary keys so there is no concerns with these clashing. http://www.access-programmers.co.uk/forums/showthread.php?t=264374

I looked into using linked tables. With the main database being called LMA and the linked tables in databases called Tablet1, Tablet2 & Tablet3. This worked on the Desktop PC. However when I downloaded Tablet1, 2 & 3 on the tablets they couldn't locate the source tables in LMA as it was still on the desktop PC!

I then tried it the other way making LMA linked tables and Tablet 1, 2, 3 as local tables but realized that the linked tables on LMA can only derive from one source table not 3.

Is it possible to have a linked/local table hybrid? So when it is not connected to the desktop it is local and when it is it adds the new data to the tables? Any other recommendations?

Thanks for your time!
James

 

[Rx_]

Oh yes, been there done that before the Internet was around.
In the old days... Access had a feature called Replication. It was a little tricky to learn how to designe to it, but wow, it worked great.
I was QA manager for a project where thousands of tool sales trucks took orders, did repairs and the whole business. When they got back to the home office, they used 56k dial up modems. We had a bank of 255 RAS modems.
So, they were stand-alone with a front-end and local Access Jet engine (1997 Access). They would connect to a remote SQL Server and update each other.
In this case, we did not use Replication, but built a custom application.

The key - You will need fields that list if the data is "dirty", meaning it changed since the last coordinated data exchange.

When you connect to central db, only the dirty data needs to be sent.
Also, a conflict manager. For example: if two users schedule the same resourse for the same time, then both connect to Central DB later, how will you manage conflicts.

The reason the old Replication Manager is mentioned, there are some execelent case usage and guidlines mentioned for design.

In fact, it is probably more common than you suspect.

 

[JDCD201]

Thanks Rx_

With regards to the "dirty data", none of the current data shall be changed on the tablets, only new data will be added. Maybe outlining the structure would help:

Form 1 - The engineer selects the:
1. Client
2. Site
3. Lift Group
4. Lift
(These will not be changed on the tablets but new ones may be added if it is a new client/site/group etc)

Form 2 - This is the inspection sheet and is filtered by the specific Lift group selected in Form 1.

Here the engineer selects the area they are inspecting such as Machine room, car top etc.

The previous inspection (e.g. 09/09/2013) for this area is shown initially as a reference but is not changed. A new inspection is started by pressing the new inspection button.

Note. They can still see the previous inspections using the back and forward arrows. This is to use them as a reminder of previous faults to check that they have been corrected this time but they are not actually changing the previous report.

It doesn't necessarily have to be done over the internet or server at all. In fact transferring the data using a USB would be just as simple. With the ID's being 100% unique a copy and paste job would do it but obviously this is very long winded with all the tables and 3 tablets worth of new data!