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]

Re: Access Replication & Synchronication vs MSDE

"Rick" <richard.overtu rf@att.net> wrote in
news:1115992719 .566870.83780@f 14g2000cwb.goog legroups.com:
[color=blue]
> 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?[/color]

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.publi c.sqlserver.rep lication 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 "replicatio n 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]

Re: Access Replication &amp; Synchronication vs MSDE

On Fri, 13 May 2005 21:30:51 GMT, "David W. Fenton"
<dXXXfenton@bwa y.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.

[color=blue]
>"Rick" <richard.overtu rf@att.net> wrote in
>news:111599271 9.566870.83780@ f14g2000cwb.goo glegroups.com:
>[color=green]
>> 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?[/color]
>
>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.publi c.sqlserver.rep lication 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 "replicatio n 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.[/color]

[David W. Fenton]

Re: Access Replication &amp; Synchronication vs MSDE

Tom van Stiphout <no.spam.tom774 4@cox.net> wrote in
news:2rma811h4h 537iv8a3s32uebp b59u26hin@4ax.c om:
[color=blue]
> 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.[/color]

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]

Re: Access Replication &amp; Synchronication vs MSDE

1) No.
2) If you want to build your own "replicatio n 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]

Re: Access Replication &amp; Synchronication vs MSDE

Rick wrote:
[color=blue]
> If I link the FE to MSDE rather to Access, can I 1.) provide a more
> secure replication and synchronization process[/color]

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]

Re: Access Replication &amp; Synchronication vs MSDE

"Chuck Grimsby" <c.grimsby@worl dnet.att.net> wrote in
news:1116156840 .057535.142880@ g14g2000cwa.goo glegroups.com:
[color=blue]
> 2) If you want to build your own "replicatio n 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.[/color]

Jet replication has the same problem in this regard.
[color=blue]
> 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.[/color]

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]

Re: Access Replication &amp; Synchronication vs MSDE


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:[color=blue]
> If I link the FE to MSDE rather to Access, can I 1.) provide a more
> secure replication and synchronization process[/color]


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]

Re: Access Replication &amp; Synchronication vs MSDE

"Rick" <richard.overtu rf@att.net> wrote in
news:1116384425 .215047.44110@o 13g2000cwo.goog legroups.com:
[color=blue]
> 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. . . .[/color]

Do not under any circumstances use the design master for editing.
And it should not be involved in regular synchronization s.

A design master is something you squirrel away and keep protected,
and synch only occasionally to make sure it doesn't expire.
[color=blue]
> . . . 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.[/color]

Post in the microsoft.publi c.access.replic ation 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