Advice on how to best synchronize data

[Silvio]

Hello everyone, I have an Access 2003 database divided in BE and FE. The BE
resides in a server and each user has a copy of the FE installed in their
computer. Now… the users would like to use they laptop in the field and want
to be able to make data entry and lookup data as needed. VPN does not seem to
be a good option because of the slow speed of the internet via wireless
modems. I was contemplating to possibility of install a modified version of
the FE pointing to data stored in the local drive C. Of course that would
create a synchronize issue - upload new data and download new and/or modified
data from the server once back in office and connected to the LAN. I never
had to accomplish such project and I would like to hear from you how to best
approach this project, handle the synchronization problem (keeping in mind
that several tables use AutoNumber as PK). Any idea, help, advice and tip
will be REALLY appreciate.

Thank you,
Silvio

 

[kelli]

Hello everyone, I have an Access 2003 database divided in BE and FE. The
BE
resides in a server and each user has a copy of the FE installed in their
computer. Now… the users would like to use they laptop in the field and
want
to be able to make data entry and lookup data as needed. VPN does not seem
to
be a good option because of the slow speed of the internet via wireless
modems. I was contemplating to possibility of install a modified version
of
the FE pointing to data stored in the local drive C. Of course that would
create a synchronize issue - upload new data and download new and/or
modified
data from the server once back in office and connected to the LAN. I never
had to accomplish such project and I would like to hear from you how to
best
approach this project, handle the synchronization problem (keeping in mind
that several tables use AutoNumber as PK). Any idea, help, advice and tip
will be REALLY appreciate.

Thank you,
Silvio

 

[Silvio]

Kelli, I am not sure I really understood your comment

 

[Mark Andrews]

I have one customer in Haiti who does this:
1. Monthly they give every remote office a new copy of the back-end (from
the central office).
Every month the process is:
- remote users press a button to make a text file
- send file to central
- central presses a button to read in file
the logic basically updates existing records and then adds in new records,
when adding in new records a new PK is created and must be applied to all
related records. So in general records reside in temp tables and are
modified and then used to update and add to existing database.
- Once all remote changes are in, goto step 1

You can export multiple tables into one file and it just adds extra columns.

The only other approach I can think of is to use some kind of GUID instead
of Access autonumbers.
If you search for Access GUID I think there are approaches listed.

If you want some of the code, I could quickly copy it for you (I can't give
you the database or any workable solution other than a quick copy of some
code if that would help). Send me an email if you want this.

Hope that gives you some ideas,
Mark
RPT Software
http://www.rptsoftware.com

 

[John Spencer MVP]

Take a look at replication. READ up on it as it works well if properly
implemented, but you have to be aware of the possibility of causing update
conflicts during the synchronization process. Implementing replication is not
a trivial task.

IF all the users need is the ability to lookup data, then replication is
one-way from the master to the replicas and that is fairly simple.

Resource tutorials and information on using replication

For Jet 3.5 look at
http://support.microsoft.com/?id=164553 - replication white paper
http://support.microsoft.com/?id=182886 - replication FAQ

For Jet 4.0
http://support.microsoft.com/?id=190766 - white paper
http://support.microsoft.com/?id=282977 - FAQ

Also see
http://www.trigeminal.com - excellent replication site
http://support.microsoft.com/support/access/content/repl/replication.asp

How to make replicable database with vba
http://msdn2.microsoft.com/en-us/library/aa140026(office.10).aspx

You might also Google "David Fenton" +Access +Replication and check out
http://dfenton.com/DFA/help.html
and follow the link to Jet Replication Wiki


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

and follow the link to Jet Replication Wiki

which is:

http://dfenton.com/DFA/Replication/

 

[David W. Fenton]

I have an Access 2003 database divided in BE and FE. The BE
resides in a server and each user has a copy of the FE installed
in their computer. Nowƒ Ý the users would like to use they laptop
in the field and want to be able to make data entry and lookup
data as needed.

If the users are always connected to the Internet when in the field,
then I would suggest hosting the app on a Windows Terminal Server.
That is simple and easy to manage.

However, if the users need to be disconnected from the Internet
while they work, then Jet replication is a good solution. It is
quite simple to put a replica on each laptop and code a direct synch
with the shared back end when the users come back to the office and
connect to the LAN.

If, however, you need to synch the data while the users are still in
the field, the process becomes at least an order of magnitude more
complex, because you need to use indirect replication, which has
outside dependencies and a properly configured and maintained
infrastructure. It's quite doable, but a lot more work to set up and
keep running smoothly than the simple scenario using direct
replication.