"Andrew Chanter" <he****@radsolutions.com.au> wrote in
news:O%*******************@news-server.bigpond.net.au:
I developed 3 different replicated database applications in MS
Access for 3 different corporate clients in Access 97 several
years ago to enable data to be shared across wide area networks.
I observed 2 issues that plagued all 3 systems.
1. the database files continually became corrupt during
synchronization. Most times this was easily fixed via a repair and
compact, but the frequency with which this occurred was of
concern, and occasionally the file would not respond to a repair
and would have to be rebuilt.
2. the conflict logs continually swell with bogus conflict
records.
Bogus? In what sense? They may look identical to *you*, but to Jet,
they aren't. Remember, Jet replication uses most-edited record (in
Jet 3.5, which is what was available in A97) to determine which
record wins when there is a tie between the records. In Jet 4 (A2K
and higher), this operates at the field level, but is still based on
most changes.
To put it another way, say this is the history:
Generation Replica 1 Replica 2
1 Value1 Value1
2 Value 1
3 Value1
Now, from a human point of view, the final state of the two records
is identical, as "Value1" = "Value1". But that's not the way it
works -- because Replica 1 has a higher generation number, it wins.
Now, assume you had an equal number of changes in both replicas
(which is what is required to have a conflict show up), and both
ended up with "Value1" as the end data value. To *you* there's
nothing to resolve, as the data in the fields is identical, but Jet
can't know that. To it, there have been 3 changes on both sides, and
it needs to ask a human being to fix it.
My guess is that you have an application that is saving records more
often than it needs to do so (each save is a generation, even if the
data hasn't been altered). The first thing to do is to check your
application to see if you have code that is saving records without
first checking if the record needs to be saved. The easiest way to
do this is to replace any code that saves records with this:
If Me.Dirty Then Me.Dirty = False
That will only save the record if it has been edited.
Now, if you have code that happens to be somehow writing data that
is unchanged (say, writing "Value1" into a field whose existing
value is already "Value1"), then you need to eliminate that as well.
I have never seen a properly designed replicated application that
was suitable for replication in the first place (i.e., a single
record under normal usage is only very rarely edited in more than
one replica) that produces very many conflicts. So my guess is that
your application has a design flaw that is producing these
conflicts, especially since from a human standpoint, the conflicting
records are identical.
(also, make sure your application is split, and don't under *any*
circumstances replicate the front end -- this can lead to all kinds
of conflicts and problems that very often lead to the loss of the
whole replicated project to corruption; additionally, make sure
you're not emailing or copying replicas around -- that's a
non-supported scenario destined to corrupt your replica set
eventually, or, at the very least, leave you with replicas that you
can't guarantee have the same data in them)
Based on this I tended to avoid recommending that any other
clients develop a system using replicated access databases.
However I have recently been approached by a client interested in
converting an existing application to a replicated system. This
system is in Access 2003. Does anyone have any experience in
replicating A2003 databases? Has MS improved this at all between
97 and 03?
I don't use replication any more except in one scenario, when there
are laptop users who have to work in the field and edit/add data
without a live Internet connection. For all remote users who can be
connected to the Internet, I host the application on Windows
Terminal Server.
In the laptop situation, if the users can wait to get back to the
office to synch with the mother ship, then they can do it with
simple direct replication (connected via LAN). If they need to synch
from their remote location, then you need to use dialup (for
indirect replication) or Internet (for Internet replication; though
if you can set up a VPN and connect to the home network via WAN, you
can use indirect replication).
Indirect replication is tricky to set up and keep running.
Internet replication is almost impossible to set up and keep running
(according to reports I've seen from people who've tried it), and I
would never consider it as it has a built-in dependency on IIS,
which I will not allow any my clients to run (at least, where I have
any say in the matter; I certainly will never recommend any
application that requires it!).
In terms of your question about improvements, replication was vastly
improved with the introduction of Jet 4 along with Access 2000. What
you got was two really major improvements:
1. field-level conflict resolution (instead of record-level)
2. elimination of data errors (they are treated as mere conflicts,
which makes them much easier to resolve).
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
