Seeking advice on replication

[dlesandrini]

I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.
I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20 mb,
and synchronization will only take place while connected to their LAN.

Does this sound like a good candidate for Replication?

If so, I'm assuming that the data and client would be combined into
one mdb file and the whole thing would be replicated, correct?

When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?

If there is only a small likelyhood that two users will edit the same
record at the same time, how often should synchronization be done
and how would it be scheduled? Is the Rep Mgr the best route or
should this be done with VBA code?

Thanks in advance for any insights you have to contribute.

Danny J. Lesandrini
dlesandrini at hotmail

[David W. Fenton]

dl*********@gmail.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.

I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20
mb, and synchronization will only take place while connected to
their LAN.

Does this sound like a good candidate for Replication?
Well, all you've told us is that it would be safely doable with
direct replication on the LAN, just using the Access UI, or
programmed through DAO (which can only do direct replication; for
indirect replication, you need the TSI Synchronizer from
http://www.trigeminal.com, and the synchronizer that comes as a part
of Replication Manager in the Developer's toolkit).

Whether or not a particular application is a good candidate depends
for Jet replication on a number of factors:

1. design of the existing data schema: certain kinds of RI setups
can break when replicated, such as a required self-referential join.

2. degree of overlap of data edits between different users -- if
most of the records are not going to be edited by more than one
person, it's not going to be an issue. But if they are, you'll have
major replication conflicts, and it will be very hard to resolve
them successfully.

The best apps for replication to multiple sites are apps where each
site/user works on a subset of data that needs to be shared with
other users read-only, but that other users won't be editing very
much. Also, if most of the changes will be added records, that works
better than lots of edits to existing records.

Of course, if you have any generated primary keys or numbering
systems that are exposed to the users, replication adds a set of
problems, as there is no centralized authority for handing out the
next value when the users are working independently.
If so, I'm assuming that the data and client would be combined
into one mdb file and the whole thing would be replicated,
correct?
Well, you'd have the standard split architecture, with a front end
with the forms/reports/queries and a back end with the data tables.
Only the back end would be replicated, because Jet replication
really only works in the long run with pure Jet data
(tables/queries) -- forms and reports and modules are all Access
objects (i.e., Jet objects with lots of special custom properties
known only to Access), and especially in Access 2K and beyond, with
the new monolithic save model (i.e., the Access project is stored in
a single record of a Jet system table, rather than in different
records for each object), replicating Access objects is very
dangerous. The same corruption problems that occur when you try to
have multiple people using the same front end are multiplied in a
replicated front end, and very often, after a very short period of
time (i.e., after a handful of synchronizations), the whole project
ends up irretrievably corrupt.

For data tables, though, Jet replication is wonderfully reliable,
assuming you don't have a workflow that is going to produce lots of
data editing conflicts.
When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?
No, because that would be in the front end, which should not be
replicated.

The issues of distributing updates to a front end are precisely the
same in a replicated application as in a non-replicated application.
If there is only a small likelyhood that two users will edit the
same record at the same time, how often should synchronization be
done and how would it be scheduled? . . .
That all depends on how often people need the data from the other
members of the replica set (or from whatever functions as the main
data file in the office).
. . . Is the Rep Mgr the best route
or should this be done with VBA code?

Since they will only be replicating over the LAN, you don't need to
worry about ReplMan. You only need that when you need to do Indirect
or Internet replication. As long as you're on a WIRED LAN (not on
wireless -- wireless is completely unacceptable for any Access
application), you can use direct replication.

It's very easy to program.

The other rookie mistake is to have the design master used for
editing. The design master should be squirrelled away and never used
for data editing. It should be synched with the other replicas
occasionally, because replicas eventually do expire (default is 1000
days, I think).

So, in the office, on the LAN, there'd be your main data editing
replica that all the front ends on the workstations connect to, and
hidden away where only an administrator will work with it, the
design master. On each laptop, there will be a replica which is
synched with the central office replica.

You might consider using ReplMan to set up a replica farm, which is
basically a collection of identical replicas that are synched in the
background on a schedule. The value of that is that if your main
data editing replica gets corrupted, you can easily replace it with
a new replica created from one of the replicas in the replica farm.
You'd only lose the data in the editing replica since the last
synch.

The shortest synch period that can be set with ReplMan is 15
minutes, so, that's not a big deal. Of course, you have to be
careful with Memo fields -- they should always be unbound in any
replicated application, because if a synch happens while a memo is
being edited, this often corrupts the memo pointer.

For details about replica farms, see Michael Kaplan's website,
http://www.trigeminal.com.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Danny J. Lesandrini]

Thank's David ... you're brilliant and articulate. That was just what I
needed though I can see I've got more research to do before I launch
into development.

I had really wanted to replicate forms and reports but I'll head your
warning. I also like the idea of performing the synch programmatically
and appreciate your comment that it's easy.

The information about keeping memo fields as unbound is also very, very
helpful. If you think of any other caveats, I'd love to be forewarned.

--
Danny J. Lesandrini
dl*********@hotmail.com
www.amazecreations.com/datafast/
"David W. Fenton" <dX********@bway.net.invalid> wrote ...

I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.

I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20
mb, and synchronization will only take place while connected to
their LAN.

Does this sound like a good candidate for Replication?

Well, all you've told us is that it would be safely doable with
direct replication on the LAN, just using the Access UI, or
programmed through DAO (which can only do direct replication; for
indirect replication, you need the TSI Synchronizer from
http://www.trigeminal.com, and the synchronizer that comes as a part
of Replication Manager in the Developer's toolkit).

Whether or not a particular application is a good candidate depends
for Jet replication on a number of factors:

1. design of the existing data schema: certain kinds of RI setups
can break when replicated, such as a required self-referential join.

2. degree of overlap of data edits between different users -- if
most of the records are not going to be edited by more than one
person, it's not going to be an issue. But if they are, you'll have
major replication conflicts, and it will be very hard to resolve
them successfully.

The best apps for replication to multiple sites are apps where each
site/user works on a subset of data that needs to be shared with
other users read-only, but that other users won't be editing very
much. Also, if most of the changes will be added records, that works
better than lots of edits to existing records.

Of course, if you have any generated primary keys or numbering
systems that are exposed to the users, replication adds a set of
problems, as there is no centralized authority for handing out the
next value when the users are working independently.

If so, I'm assuming that the data and client would be combined
into one mdb file and the whole thing would be replicated,
correct?

Well, you'd have the standard split architecture, with a front end
with the forms/reports/queries and a back end with the data tables.
Only the back end would be replicated, because Jet replication
really only works in the long run with pure Jet data
(tables/queries) -- forms and reports and modules are all Access
objects (i.e., Jet objects with lots of special custom properties
known only to Access), and especially in Access 2K and beyond, with
the new monolithic save model (i.e., the Access project is stored in
a single record of a Jet system table, rather than in different
records for each object), replicating Access objects is very
dangerous. The same corruption problems that occur when you try to
have multiple people using the same front end are multiplied in a
replicated front end, and very often, after a very short period of
time (i.e., after a handful of synchronizations), the whole project
ends up irretrievably corrupt.

For data tables, though, Jet replication is wonderfully reliable,
assuming you don't have a workflow that is going to produce lots of
data editing conflicts.

When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?

No, because that would be in the front end, which should not be
replicated.

The issues of distributing updates to a front end are precisely the
same in a replicated application as in a non-replicated application.

If there is only a small likelyhood that two users will edit the
same record at the same time, how often should synchronization be
done and how would it be scheduled? . . .

That all depends on how often people need the data from the other
members of the replica set (or from whatever functions as the main
data file in the office).

. . . Is the Rep Mgr the best route
or should this be done with VBA code?

Since they will only be replicating over the LAN, you don't need to
worry about ReplMan. You only need that when you need to do Indirect
or Internet replication. As long as you're on a WIRED LAN (not on
wireless -- wireless is completely unacceptable for any Access
application), you can use direct replication.

It's very easy to program.

The other rookie mistake is to have the design master used for
editing. The design master should be squirrelled away and never used
for data editing. It should be synched with the other replicas
occasionally, because replicas eventually do expire (default is 1000
days, I think).

So, in the office, on the LAN, there'd be your main data editing
replica that all the front ends on the workstations connect to, and
hidden away where only an administrator will work with it, the
design master. On each laptop, there will be a replica which is
synched with the central office replica.

You might consider using ReplMan to set up a replica farm, which is
basically a collection of identical replicas that are synched in the
background on a schedule. The value of that is that if your main
data editing replica gets corrupted, you can easily replace it with
a new replica created from one of the replicas in the replica farm.
You'd only lose the data in the editing replica since the last
synch.

The shortest synch period that can be set with ReplMan is 15
minutes, so, that's not a big deal. Of course, you have to be
careful with Memo fields -- they should always be unbound in any
replicated application, because if a synch happens while a memo is
being edited, this often corrupts the memo pointer.

For details about replica farms, see Michael Kaplan's website,
http://www.trigeminal.com.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in
news:7t********************@comcast.com:

Thank's David ... you're brilliant and articulate. That was just
what I needed though I can see I've got more research to do before
I launch into development.
Replication is *very* complicated. I'd suggest that you memorize
everything about replication on Michael Kaplan's website,
http://www.trigeminal.com, because he was the authority on the
subject, and no one has emerged who is ever going to know as much
about it as he.

I'd also suggest that you browse the Google archive of
microsoft.public.access.replication. You're already posting there,
so you know it exists, but everything that ever could happen with
regard to replication has been discussed there, and for any topic,
MichKa has given a definitive answer at one time or the other.
I had really wanted to replicate forms and reports but I'll head
your warning. . . .
It simply doesn't work. If you understand how replication works and
how the Access 2K+ project save model works, you'd quickly realize
it would be skating on thin ice to even consider it.
. . . I also like the idea of performing the synch
programmatically and appreciate your comment that it's easy.
It's actually this simple:

Dim strLocal As String ' the local replica
Dim strRemote As String ' the remote replica
Dim db As DAO.Database

Set dbSynch = DBEngine(0).OpenDatabase(strLocal)
db.Synchronize strRemote

db.Close
Set db = Nothing

That's all there is to it.

Of course, you have to figure out the local replica name and the
remote replica name, but that's pretty easy (the local one could be
gotten from any linked table, while you would probably be using the
same hub replica for all the remote synchs).

Now, this doesn't account for conflicts. You'd probably want to loop
throught the .TableDefs collection of both the local database to see
if there are any conflicts (you just check for any table names that
end in "_Conflict", and then you have to decide how to handle it. If
you're not rolling your own conflict viewer, then you'll have to
prompt the user to open the data files and resolve conflicts.

However, you may not want this to be something that's done by
ordinary users, and leave it to an administrator. So, the conflict
check might advise the user to contact the administrator.

The great thing about Jet 4 conflicts is that you don't have to
resolve them on both sides, as in Jet 3.5 -- you resolve them in
your local replica and then synch again, and it propagates the
results to the remote replica.
The information about keeping memo fields as unbound is also very,
very helpful. If you think of any other caveats, I'd love to be
forewarned.

I can't think of anything else in particular. But as you encounter
issues, post here. I have all threads with "replication" in the
subject flagged and try to reply to them here so that people don't
think there is no one around who knows how it works.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc