472,990 Members | 3,037 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,990 software developers and data experts.

Setting up replication with access front end

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
6 3801
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
"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
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
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
"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Beda Christoph Hammerschmidt | last post by:
MySQL, Access, ODBC, Replication Hello, i have a central mysql database that is accesed by multiple clients running MS Access with ODBC. This works fine if Access has a permanent connection...
4
by: Andi Plotsky | last post by:
I need to synchronize 3 databases (1 Master and 2 replicas). I thought I'd give the Replica feature in Access2000 a whirl. I'm not sure I'm THAT impressed.....but maybe I'm just doing something...
1
by: Andrew Chanter | last post by:
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...
2
by: adb | last post by:
I came up with a replication configuration that is basically the result of all the restrictions of replication as well as the restrictions of allowable software on work PC's and I was curious if...
3
by: SimonDB | last post by:
The replication of my database was successful and it works well. I can modify tables and forms, except for one thing : VBA ! I canít add code in VBA, the error message : "incorrect data format". I...
3
by: dlesandrini | last post by:
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. ...
56
by: Raphi | last post by:
Hi, I've been using an Access application I wrote for an office with the front-end stored on all computers and the back-end on one of them serving as an Access file server. Now we're moving...
8
by: Rick | last post by:
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...
8
by: Sharktbbtfy | last post by:
Hi, hoping someone can help. The situation is that an app I designed is installed on a desktop PC and six laptops,separately, which synchronise to the desktop once a day at random times. I...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.