By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,997 Members | 2,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,997 IT Pros & Developers. It's quick & easy.

Setting up replication with access front end

P: n/a
Hi

We have an access app (front-end+backend) running on the company network. I
am trying to setup replication for laptop users who go into field and need
the data synched between their laptops and the server upon return to the
office. I am planning it this;

Move all access tables to sql server and then link the tables to access
front-end mdb app (using odbc?). Copy the same setup (access front end + sql
backend) onto each laptop. Setup replication between the server sql server &
each laptop's sql server. My question is; would this sort of setup work? Is
there a better way to do this?

Thanks

Regards
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
It will work - I've done something very similar myself. But (depending
on your data design) it won't necessarily be a snap. And be very, very
careful of triggers.

Edward

Nov 13 '05 #2

P: n/a
"John" <Jo**@nospam.infovis.co.uk> wrote in
news:42*********************@news-text.dial.pipex.com:
We have an access app (front-end+backend) running on the company
network. I am trying to setup replication for laptop users who go
into field and need the data synched between their laptops and the
server upon return to the office. I am planning it this;

Move all access tables to sql server and then link the tables to
access front-end mdb app (using odbc?). Copy the same setup
(access front end + sql backend) onto each laptop. Setup
replication between the server sql server & each laptop's sql
server. My question is; would this sort of setup work? Is there a
better way to do this?


Why are you moving the data to SQL Server?

Jet can do replication, and it's a lot easier to manage than SQL
Server replication, as well as more flexible.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3

P: n/a
I would love to use access replication if it is easier and more flexible. Is
there any advantage of using sql server replication over access replication?
Just to see both sides.

Thanks

Regards

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78...
"John" <Jo**@nospam.infovis.co.uk> wrote in
news:42*********************@news-text.dial.pipex.com:
We have an access app (front-end+backend) running on the company
network. I am trying to setup replication for laptop users who go
into field and need the data synched between their laptops and the
server upon return to the office. I am planning it this;

Move all access tables to sql server and then link the tables to
access front-end mdb app (using odbc?). Copy the same setup
(access front end + sql backend) onto each laptop. Setup
replication between the server sql server & each laptop's sql
server. My question is; would this sort of setup work? Is there a
better way to do this?


Why are you moving the data to SQL Server?

Jet can do replication, and it's a lot easier to manage than SQL
Server replication, as well as more flexible.

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

Nov 13 '05 #4

P: n/a
PS: Does access replication work over an internet/vpn connection?

Thanks

Regards

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78...
"John" <Jo**@nospam.infovis.co.uk> wrote in
news:42*********************@news-text.dial.pipex.com:
We have an access app (front-end+backend) running on the company
network. I am trying to setup replication for laptop users who go
into field and need the data synched between their laptops and the
server upon return to the office. I am planning it this;

Move all access tables to sql server and then link the tables to
access front-end mdb app (using odbc?). Copy the same setup
(access front end + sql backend) onto each laptop. Setup
replication between the server sql server & each laptop's sql
server. My question is; would this sort of setup work? Is there a
better way to do this?


Why are you moving the data to SQL Server?

Jet can do replication, and it's a lot easier to manage than SQL
Server replication, as well as more flexible.

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

Nov 13 '05 #5

P: n/a
"John" <Jo**@nospam.infovis.co.uk> wrote in
news:42*********************@news-text.dial.pipex.com:
I would love to use access replication if it is easier and more
flexible. Is there any advantage of using sql server replication
over access replication? Just to see both sides.


Well, the advantages are all in the differences between SQL Server
and Jet itself, absent replication issues. Where SQL Server is
required or preferable, its replication is likely to be more
desirable. Where Jet is fine, you'll probably be better off staying
with Jet.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

P: n/a
"John" <Jo**@nospam.infovis.co.uk> wrote in
news:42*********************@news-text.dial.pipex.com:
PS: Does access replication work over an internet/vpn connection?


Yes, but with caveats:

1. direct replication (the kind you use through the Access user
interface) is unacceptable for two reasons:

a. it's very slow because it has to pull the remote file across
the wire and open it in the local memory in order to do the
synch.

b. because of a), it's very, very prone to corrupting one or both
replicas, causing them to either lose replicability (which cannot
be restored without losing the ability to synch with the original
replica set) or corrupt in even worse ways.

Direct replication is just fine on a LAN, however.

2. there are two methods of non-direct replication:

a. indirect replication -- runs across an SMB networking
connection, i.e., dialup networking or a VPN across the Internet,
and uses SMB networking to exchange the data files.

b. internet replication -- requires IIS on both ends and open FTP
server on both ends to exchange the data files.

Neither form of non-direct replication opens the remote file.
Instead, on both ends of the exchange, a local process opens the
local database and creates message files, each with a single
generation of changes to the local replica. This process then sends
those message files to the remote process (by writing them to the
remote dropbox), which then applies them to the local replica. This
kind of exchange is very efficient because only the changes are
sent/received. It's also very safe because nothing is open across
the network connection.

I think Internet replication is unacceptable because it's a massive
security risk that also adds a huge number of tasks to administering
the remote machines -- you have to keep multiple machines running
with IIS and FTP services running. Since Blaster, you really don't
want IIS running on any more machines than necessary, and FTP is
really not an acceptable file transfer method any more -- it's too
insecure (SCP or SFTP is really what should be used, but I doubt MS
is going to revise Jet Internet replication to account for this).

Indirect replication, on the other hand, is not quite so hard to
administer, though it's not nearly as easy as administering an
application hosted on a Windows terminal server. You do have to have
the synchronizer running on all machines on either end of
synchronizations, but it's a simple, low-profile process. One big
disadvantage of it on your central server is that it cannot be run
as a service, so you are required to run it on the server in a user
logon and then lock the console. I don't consider that particularly
acceptable for a server, but it's the best you can do with indirect
replication.

Both types of indirect replication are completely dependent on the
proper establishment of read/write access to the dropboxes on both
ends, and for proper authentication upon logon of the remote
machine.

Now, if your remote users don't need to synch with the mother ship
except when they are back in the office, you don't need to muck
around with any of the above -- you can just have them do a direct
replication once they get back to the central office and connect to
the LAN.

If they need to edit data when out of the office, the choice of
solutions depends on what kind of access you have available. If, for
instance, the traveller is always working from a client or branch
office that can provide Internet access for free, then it makes more
sense to me to have them simple run the app remotely, with Windows
Terminal Server and remote desktop connection.

If, on the other hand, the only accessible Internet connection on
the road is very expensive (such as a hotel or Internet cafe), and
they really need to work offline and connect only occasionally, then
indirect replication over VPN seems to me to be the most desirable
solution. Before VPNs were common, Internet replication was what
most people used in that circumstance, but now VPNs are pretty
common, and if you don't have one, you probably really ought to! And
that's even if you're just hosting the app on Terminal Server!

But, replication in general, and indirect replication in particular,
are both complicated with a lot of considerations and gotchas,
running the gamut from simply requiring wise planning in your data
schema (a schema that works perfectly in a non-replicated app can
cause enormous problems if converted to replication -- indeed, it
can completely not work), but also lots of cooperation with your
networking people. Also, there's the natural resistance in an IT
department to all the administrative issues raised by running the
Jet synchronizer, both on travelling laptops, and also on the
centralized server. Then there's all the troubleshooting it takes to
get all the user permissions and file access right. It really
requires someone who understands Access and replication and also
network administration.

That's why I think it's not often a very good choice.

But if you can limit the synchs to when the users are back in the
office, it's really a piece of cake, using direct replication, and
requires very little initial setup.

All that said, my definite preference overall is for hosting the app
on a Terminal Server, and I'd only *not* do that where the Internet
access costs were really, really high. The reason is that
engineering it to get around those access costs requires
implementing systems on remote machines that are much harder (and
more expensive) to administer, as well as costing a lot to develop
and test.

Of course, the more remote users you have to spread the development
costs over, the lower the per user cost for developing an Indirect
replication application. You still are buying yourself a lot of
administrative cost in keeping the laptops running (as well as
potential server-side issues like the Exchange hotfix that caused
Jet databases to lose replicability every couple of hours or so).
Over the long run, it might just be easier to pay for the Internet
access than to have the constant drip, drip, drip of administrative
care and feeding that replication actually requires.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.