Access Replication & Synchronication vs MSDE

[Rick]

I'm running an Access2000 front end mde linked to an Access2000 back
end mdb. I need to set up replication and synchronization of only the
data tables between the master db on the pc and replicas on numerous
laptops.

I know that it can be done using the Access replication manager
(although without a simple UI in the front end mde), but the question
is:

If I link the FE to MSDE rather to Access, can I 1.) provide a more
secure replication and synchronization process, and 2.) can I build a
UI into the front end mde to simplify the process for non-techie users?
Thanks

[David W. Fenton]

"Rick" <ri**************@att.net> wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:

I'm running an Access2000 front end mde linked to an Access2000
back end mdb. I need to set up replication and synchronization of
only the data tables between the master db on the pc and replicas
on numerous laptops.

I know that it can be done using the Access replication manager
(although without a simple UI in the front end mde), but the
question is:

If I link the FE to MSDE rather to Access, can I 1.) provide a
more secure replication and synchronization process, and 2.) can I
build a UI into the front end mde to simplify the process for
non-techie users?

SQL Server replication is, from what I've read, more complicated to
set up and more limited in its capabilities than Jet replication.
That is, you have a narrower set of valid configurations that will
work well.

I can't see how you'd be getting any benefit at all from switching
to MSDE, and you'd probably be buying yourself a lot of headaches.
In general, I don't get the impression that very many people are
doing SQL Server replication. You might want to go to Google Groups
and check out microsoft.public.sqlserver.replication to get a flavor
for it. I have no experience with it, but I've tried to read up on
it over the years, since I'm always planning my Jet apps with an eye
towards the possibility of upgrading to SQL Server (I just have
never had any clients who both need the upgrade and want to spend
the money to do it!).

As to UI, I don't know why you bring "replication manager" into the
equation. You don't need ReplMan to do direct replication on a LAN,
and you aren't required to design a UI for it, as the one provided
in Access is sufficient to get the job done, assuming all your
synchs are taking place over the LAN in the main office.

Now, you may want to make it a little more user friendly, you may
want to add functionality to switch the links in the front end on
the laptop from the laptop replica to the shared server hub replica
after users have returned to the office and synched with the mother
ship (and vice versa when they are leaving the office). You may want
to create something to address conflicts (though that's quite
complex).

But I've never had a situation where I did much of any of this.

But none of it is very complicated.

And none of it would be easier with MSDE.

Indeed, it would probably be much more work, especially if you've
got little experience working with SQL Server.

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

[Tom van Stiphout]

On Fri, 13 May 2005 21:30:51 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I agree with most of what David said, except with "none of it is very
complicated". Access replication is an advanced topic. It only works
well if you RIGOROUSLY conform to the requirements. See trigeminal.com
for some backgrounds.
I only recommend it to very sophisticated clients.

-Tom.

"Rick" <ri**************@att.net> wrote in
news:11*********************@f14g2000cwb.googlegr oups.com:

I'm running an Access2000 front end mde linked to an Access2000
back end mdb. I need to set up replication and synchronization of
only the data tables between the master db on the pc and replicas
on numerous laptops.

I know that it can be done using the Access replication manager
(although without a simple UI in the front end mde), but the
question is:

If I link the FE to MSDE rather to Access, can I 1.) provide a
more secure replication and synchronization process, and 2.) can I
build a UI into the front end mde to simplify the process for
non-techie users?

SQL Server replication is, from what I've read, more complicated to
set up and more limited in its capabilities than Jet replication.
That is, you have a narrower set of valid configurations that will
work well.

I can't see how you'd be getting any benefit at all from switching
to MSDE, and you'd probably be buying yourself a lot of headaches.
In general, I don't get the impression that very many people are
doing SQL Server replication. You might want to go to Google Groups
and check out microsoft.public.sqlserver.replication to get a flavor
for it. I have no experience with it, but I've tried to read up on
it over the years, since I'm always planning my Jet apps with an eye
towards the possibility of upgrading to SQL Server (I just have
never had any clients who both need the upgrade and want to spend
the money to do it!).

As to UI, I don't know why you bring "replication manager" into the
equation. You don't need ReplMan to do direct replication on a LAN,
and you aren't required to design a UI for it, as the one provided
in Access is sufficient to get the job done, assuming all your
synchs are taking place over the LAN in the main office.

Now, you may want to make it a little more user friendly, you may
want to add functionality to switch the links in the front end on
the laptop from the laptop replica to the shared server hub replica
after users have returned to the office and synched with the mother
ship (and vice versa when they are leaving the office). You may want
to create something to address conflicts (though that's quite
complex).

But I've never had a situation where I did much of any of this.

But none of it is very complicated.

And none of it would be easier with MSDE.

Indeed, it would probably be much more work, especially if you've
got little experience working with SQL Server.

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:2r********************************@4ax.com:

I agree with most of what David said, except with "none of it is
very complicated". Access replication is an advanced topic. It
only works well if you RIGOROUSLY conform to the requirements. See
trigeminal.com for some backgrounds.
I only recommend it to very sophisticated clients.

I agree with what you are saying, but my comment "none of it is very
complicated" was not about *replication*, per se, but about the
amount of programming necessary to create your own UI for direct
replication. That should have been quite clear from the context.

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

[Chuck Grimsby]

1) No.
2) If you want to build your own "replication process" you certainly
can. I've done it, but I'm not sure I'd ever do it again.

The problem with #2 happens when 2 (or more) "remote databases" have
changes to the same record. Which record is the "most current"?

Assuming that you have set up some sort of methodology to _track_
changed records which also records when a record was changed, you still
might not be able to answer the question. Salesman A modified the
record at 5:00pm, Salesman B changed it at 11:00pm. Is it the same
change? A newer change? At first blush, it may seem so, but Salesman
B isn't always as dilligent at doing their data entry as Salesman A is,
the change they made at 11:00pm might be a change that was made 2 days
ago, they are finally getting around to entering in. An even newer
change needs to be made, but they haven't quite "got to" doing that
yet. But they want to re-sync because they need new data for some
other company, and the changes they made to yet a different company
needs to be made and distributed to the rest of the sales force.

Company policies, no matter how severly worded, will address such
issues. People tend to do whatever they feel like.

More fun and games occur when only some fields can be altered by some
people, and others by a seperate set.

All in all, rather then replication, it's far more intelligent to set
up a website where those who need to make changes can "dial in to" and
do whatever they need to do, as well as getting the most up-to-date
information.

[Trevor Best]

Rick wrote:

If I link the FE to MSDE rather to Access, can I 1.) provide a more
secure replication and synchronization process

MSDE can only be a replication subscriber, not a publisher. To publish a
replica you need SQL Server Standard Edition or above.

--
[Oo=w=oO]

[David W. Fenton]

"Chuck Grimsby" <c.*******@worldnet.att.net> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

2) If you want to build your own "replication process" you
certainly can. I've done it, but I'm not sure I'd ever do it
again.

The problem with #2 happens when 2 (or more) "remote databases"
have changes to the same record. Which record is the "most
current"?

Assuming that you have set up some sort of methodology to _track_
changed records which also records when a record was changed, you
still might not be able to answer the question. Salesman A
modified the record at 5:00pm, Salesman B changed it at 11:00pm.
Is it the same change? A newer change? At first blush, it may
seem so, but Salesman B isn't always as dilligent at doing their
data entry as Salesman A is, the change they made at 11:00pm might
be a change that was made 2 days ago, they are finally getting
around to entering in. An even newer change needs to be made, but
they haven't quite "got to" doing that yet. But they want to
re-sync because they need new data for some other company, and the
changes they made to yet a different company needs to be made and
distributed to the rest of the sales force.
Jet replication has the same problem in this regard.
Company policies, no matter how severly worded, will address such
issues. People tend to do whatever they feel like.

More fun and games occur when only some fields can be altered by
some people, and others by a seperate set.

All in all, rather then replication, it's far more intelligent to
set up a website where those who need to make changes can "dial in
to" and do whatever they need to do, as well as getting the most
up-to-date information.

Or, Windows Terminal Server, which is a lot less work than writing a
browser-based application to replace your Access app.

But sometimes, that's just not possible, as the remote worker is not
going to be able to connect to the Internet affordably to do the
work. In those cases, replication is going to work just fine.

But, again, replication works best when:

1. each remote user tends to work on their own dataset, so that
records tend not to get edited by multiple users, AND

2. the remote users are adding records more than they are editing.

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

[Rick]

Thanks Trevor, thats exactly what I needed to know.

I am doing this project for tsunami victims in Indonesia, and my client
does not have an internet connection through which to run terminal
services, so a hardwire connection between the pc with the design
master is the only way to connect replicas on latops back to the
design master on the pc. Since there are 3 laptops (with different
data sets) connecting to the one design master pc, the chance to
conflicts would be minimal. The greatest difficulty that I have found
so far is finding information about building a user interface that
would be easy for the user to use and understand without having
non-technical peoeple trying to go through replication manager.
Trigeminal had some useful information, but not enough to put it all
together. All I want is two command buttons on the design master front
end db that for create replica and synchronize replica. Seems like it
shouldn't be that hard, and that others would have already built
something similar.

Thanks for your post, Trevor. Looks like I am stuck with Access
replication.
Rick wrote:

If I link the FE to MSDE rather to Access, can I 1.) provide a more
secure replication and synchronization process

MSDE can only be a replication subscriber, not a publisher. To publish
a
replica you need SQL Server Standard Edition or above.

[David W. Fenton]

"Rick" <ri**************@att.net> wrote in
news:11*********************@o13g2000cwo.googlegro ups.com:

I am doing this project for tsunami victims in Indonesia, and my
client does not have an internet connection through which to run
terminal services, so a hardwire connection between the pc with
the design master is the only way to connect replicas on latops
back to the design master on the pc. Since there are 3 laptops
(with different data sets) connecting to the one design master pc,
the chance to conflicts would be minimal. . . .
Do not under any circumstances use the design master for editing.
And it should not be involved in regular synchronizations.

A design master is something you squirrel away and keep protected,
and synch only occasionally to make sure it doesn't expire.
. . . The greatest difficulty
that I have found so far is finding information about building a
user interface that would be easy for the user to use and
understand without having non-technical peoeple trying to go
through replication manager. Trigeminal had some useful
information, but not enough to put it all together. All I want is
two command buttons on the design master front end db that for
create replica and synchronize replica. Seems like it shouldn't
be that hard, and that others would have already built something
similar.

Post in the microsoft.public.access.replication newsgroup. That's
where the people with experience post.

You might also want to check Google Groups.

I've never used the TSI synchronizer myself, as the only project
where I needed it was already up and running with Replication
Manager, and only two people actually did the synchronizing.

I played around with the TSI synchronizer and it didn't seem that
complicated, but I never actually coded for it, and it was years
ago.

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