Access DB over a WAN
Eric Krauss
about 7 users currently on the app and it is split to a front-end /
back-end format. The back end is located on the NT 4.0 server, and
the front end is located on the C: drive on each of the users
workstations. Six of the users are located in the corporate office
and they are fine, but one of the users are located in another state
and are linked up over a WAN. The user working over the WAN is
experiencing high network latency. Is there anything I can do to
speed up the app at the programming level? I have kicked around a
couple ideas but nothing has helped. Please e-mail me with any ideas.
Thanks,
Eric Krauss
PC Database Analyst
Midland Credit Management
Gregory Scott
MFA
M
"Eric Krauss" <eric....@mcmcg.com> wrote in message
news:caab8750.01080...@posting.google.com...
Eric Krauss
"Gregory Scott" <LionSo...@Hotmail.com> wrote in message news:<9kpdj3$2a3$1...@lust.ihug.co.nz>...
Steve Jorgensen
remote clients, either set up a separate box for each remote user to
access with PC Anywhere or VNC, or set up a terminal server such as
Microsoft Terminal Services or Citrix Metaframe.
Access is neither reliable nor fast enough over a WAN no matter what
you do, and a WAN connection will not be reliable enough for on-line
replication either, though you could consider writing your own
replication system that sends zipped export files back and forth. A
really carefully written app could perform reasonably with an Access
front end to a SQL Server back-end, but that would mean about a 60%
rewrite, and it still would work worse and be harder to maintain than
if you used remote control.
On 7 Aug 2001 10:43:47 -0700, eric....@mcmcg.com (Eric Krauss)
wrote:
Larry Linson
http://groups.google.com for the keyword "file-server". Depending on the
speed of your WAN, it may be impossible to obtain the response you want;
but, it may well be possible to modify your application to improve the speed
you are getting now.
Also, I advise you to go to a well-stocked bookstore and obtain a copy of
_Access <versionnumber> Developer's Handbook_ by Litwin, Getz, and Gilbert,
published by Sybex and read carefully what they have to say about
performance in a multiuser environment.
If you would like my views on what topics are important, the Power Point
slides I used for a presentation to my user group on the subject can be
downloaded from http://members.tripod.com/appdevissues/downloads.htm. There
was, of course, discussion along with each slide, so these do not 'tell the
complete story', but do indicate the subject matter that I covered. No, I
don't have a document of that discussion, nor speaker notes.
P.S. Please see our FAQ at http://www.mvps.org/access/netiquette.htm
regarding requests for e-mail response to questions posted in the newsgroup.
I've e-mailed you a copy of this newsgroup posting, but some participants
here will not even bother to respond to an article with reply requested by
e-mail.
"Eric Krauss" <eric....@mcmcg.com> wrote in message
news:caab8750.01080...@posting.google.com...
Larry Linson
continuation of the discussion might be of interest to others, so I'm moving
it back to the newsgroup.)
Of course, we also have to realize that, the (comparatively low) capacity of
that T-1 must be shared between the users who are connected by that line, so
each may be getting far below the nominal 1.5MBPS transfer. I would be very
reluctant to attempt implementation of an Access multiuser database where
multiple users are connected by such a WAN.
I worked for several years on an Access 2.0 client application to an
Informix server database. For part of that time, there were several users at
each of three locations whose connection was by 56KB leased line. They
managed, but performance was abominable, even with a server database. Once
the client(of all things, a Fortune 50 telephone / telecommunications
company) finally changed that to direct T-1 connections for those three
remote locations, the users were quite pleased with performance.
I would think, given your circumstances, that I would consider developing
using the Desktop/Developer edition of MS SQL Server and deploy using MSDE.
If performance is not adequate, or if your user audience grows, then it will
be a simple matter to replace MSDE with SQL Server. It is, after all, SQL
Server with an internal limit of 5 concurrent operations / 'active'
connnections, and without the nice administrative tools. Roger Jennings, in
his book 'Special Edition -- Using MS Access 2000' reported observing 25
concurrent users of Access as a client to MSDE during the beta test period
for Access 2000. He didn't state it as a limit, though.
Another alternative would be to create an application on a corporate
Intranet, using FrontPage 2002 with the Data Results Wizard and Data
Interface Wizard if that provides sufficient capability , Active Server
Pages (.asp), or Access 2002 with Data Access Pages (provided your users
will have Office XP and be using IE 5.x or later). I strongly believe that
the way an Access/Jet database is used on a web server, essentially with the
web server code doing 'routing' but accessing the database almost as a
single user, it will handle many, many more users. And, of course, a shared
T-1 connection is more than adequate for a web interface.
I have seen multiple sessions of the earlier-mentioned Access 2 client
running nicely on a small server with Citrix and NT 3.x. That was an
experiment done for the client company, and it was not stress-tested. For 30
concurrent users, given that all the sessions are running on the server, you
may need a fairly substantial slice of a fairly substantial server. On the
other hand, if only a few of your 30 users are connected via T-1, they would
be the only ones necessarily using the database via Microsoft Terminal
Services/Citrix, and everyone else could be running on the LAN in
traditional multiuser environment. And, it might be that an even simpler
solution such as pcAnywhere, ReachOut, or the equivalent might serve without
going to the lengths of using MTS / Citrix.
-----Original Message-----
I tried all the little tricks you mentioned in your last e-mail and still
nothing seems to be working. In all, after I finish my current project, I
will have about 30 users over 4 applications, which are intertwined to some
extent. Do you think maybe the next logical step would be to get an SQL 7.0
server? BTW, we have a citrix server, but it in our other location and not
in the corporate office.
Thanks,
Eric
David W. Fenton
<caab8750.01080...@posting.google.com>:
>Is replication a reliable tool in Access?
With proper care and feeding and a well-maintained and properly
configured network infrastructure, yes. I have 5 clients using it
on a regular basis, one who has two main offices (NYC and London),
two telecommuters working out of the NYC office and two laptops. It
works fine, but the learning curve is relatively steep, and lots of
things can go wrong.
Resources:
Newsgroup: microsoft.public.access.replication
Website: http://www.trigeminal.com
Between those two and the Microsoft documentation, you should learn
all you need to get started. I would recomment that you first read
the MS docs, then read MichKa's website, then browse the newsgroup
on Google. Lots of things have emerged as issues since the original
MS docs were produced.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton
<3b706856...@news.coho.net>:
>Access is neither reliable nor fast enough over a WAN no matter
>what you do, and a WAN connection will not be reliable enough for
>on-line replication either, though you could consider writing your
>own replication system that sends zipped export files back and
>forth.
Um, Steve? You don't know what you're talking about.
A WAN is *fine* for replication. Indeed, it's superb.
Indirect replication is efficient enough to work reliably and
safely over 28.8 dialup.
But the key is: *Indirect* replication.
Dale Geffs
acceptable performance. Replication will probably cause you enough
grief to make it worthless. WTS and Citrix are great solutions but may
be a bit pricey for one user. PCA is cheap and might do the trick.
Dale (delete NS to reply via email)
Steve Jorgensen
briefcase files, or through some other intermediate file type?
David W. Fenton
>Well, I guess I did it again. Is indirect replication done
>through briefcase files, or through some other intermediate file
>type?
They are through an intermediate file type, message files.
On each end of the synchronization, the synchronizer process runs.
There is a dropbox for each synchronizer. Synchronizer A sends
message files to Synchronizer B's drop box and Synchronizer B sends
message files to Synchronizer A's drop box. Synchronizer A applies
the changes in the messages in its drop box to the replicas it is
managing, and Synchronizer B does likewise. For any single indirect
synchronization session, several MSG files and several MDB files
are created in the two drop boxes, and there are several phases of
it (A sends to B, B applies changes, B sends to A, A applies
changes, A sends to B again, B applies these new changes, etc.),
especially when there are both design changes and data updates.
The exchange is extremely efficient. The client of mine with
offices in NYC and London has a single-channel ISDN line (64K) and
they synchronize twice a day (when they bother). The whole process
generally takes about 10 minutes. Even for their dialup users (28.8
because their dialin server software predates 56K modems) generally
take only about 15 minutes for a full synch at most, and they often
don't synch every day.
It's not only extremely efficient, but it's extremely safe, as only
the local synchronizer has the MDB open -- all communication across
the network is between the synchronizers, and via the file system
(i.e., the drop boxes).
On a WAN, it should work just fine and dandy, especially on a T1 or
DSL, where it would be substantially faster than the single-channel
ISDN and 28.8 dialups that my clients have used.
It really is worth the effort to investigate this. It's an awfully
good way to support the occasional telecommuter, as long as you can
control the network setup (replication falls over pretty easily
when the network is not properly configured, but with no damage to
the data, of course; the problem is that it's often difficult to
tell exactly what's causing the failure; in my experience,
replication failures are almost always due to network
misconfiguration).
David W. Fenton
<ver5ntch2i8eo1b42...@4ax.com>:
>I tend to agree with Steve. On most WAN's I've seen you will never
>get acceptable performance. Replication will probably cause you
>enough grief to make it worthless. WTS and Citrix are great
>solutions but may be a bit pricey for one user. PCA is cheap and
>might do the trick.
In regard to replication, I completely disagree.
It works just great *if* you use it as it was designed. Michael
Kaplan's website gives you all the advice you need to keep your
setup kosher (http://www.trigeminal.com).
Those who've had problems with replication either are mis-using it
or have not properly prepared themselves for using it. I've been
deploying replicated apps since 1997 and except for some errors I
made in my initial work (in early 1998 I still thought it was OK to
email replicas and replicate direct via dialup; I quickly learned
these were both very bad ideas!).
I think the key mistake most people make with replication is trying
to use it to push front-end updates out to users. Jet replication
is good only for data, not for Access objects. For data, it's rock
solid, efficient and safe as long as the network infrastructure
remains properly configured.
David W. Fenton
>I tend to agree with Steve. On most WAN's I've seen you will never
>get acceptable performance. Replication will probably cause you
>enough grief to make it worthless. WTS and Citrix are great
>solutions but may be a bit pricey for one user. PCA is cheap and
>might do the trick.
Oh, and let me add:
Replication is much cheaper than WTS/Citrix, and once a replication
strategy is designed and implemented, almost without any
administration issues (in comparison to WTS).
It's is also greatly preferable to remote control in that it leaves
you working with local data.
That said, it is inappropriate for these scenarios:
1. you need up-to-date data: if you can't wait half a day or a
couple of hours to have everyone's data, it's probably not a viabl
solution.
2. if the computers involved are not networked at least some of the
time (even via dialup networking), Jet replication is unworkable.
3. if the data updates are such that there will be lots of data
collisions: if any one record is going to be updated frequently in
more than one location, it probably won't work. However, the type
of updates may make it possible, especially with Jet 4
replication, where the conflict resolution is at the field level
and not just the record level (as in Jet 3.x replication).
Dale Geffs
data was pretty poor. Of course that could have been due to a number
of factors outside of replication itself. I gave up pretty early after
the first few problems. I was having trouble with three sites and
could not imagine the hassles that might occur with 35 or so.
You are correct about the cost of WTS or Citrix. Our network people
had just installed Citrix for our ERP system just about the time I was
fooling around with replication. I ran some tests and never looked
back. Since the system was already in place I just used it to my
advantage. However, it would be nuts to consider this solution for one
location unless money was no object or performance was the only
criteria.
I don't understand why working with local data would be preferable
unless the remote location was running disconnected from the wire most
of the time. We find a centralized database with up to the second data
to be very valuable. I suppose it depends on what you are doing
whether this is relevant. Our Citrix performance (not PCA) is actually
better than some of our stand alone systems due to the old
workstations involved.
Larry Linson
> You are correct about the cost of WTS or Citrix. ...
> ... However, it would be nuts to consider this solution for one
> location unless money was no object or performance was the only
> criteria.
Some use pcAnywhere or ReachOut for remote access from just a few locations.
My observation is that you'll clearly realize you aren't locally connected,
but patient users can "make do" with that solution.
Steve Jorgensen
384K/384K at the server end, and 768/256K at the client end, and can
barely discern the lag under normal conditions. It gets slow when a
link is having technical problems sometimes.
Dale Geffs
terms of performance. The original poster wanted a way to increase the
speed for one user. Perhaps he should blow off the WAN and connect via
VPN to a broadband connection and run a remote control scenario like
Citrix or PCA. I'll bet PCA would fly using this method. I know Citrix
does.
Eric Krauss
I have tested it from our San Diego to Phoenix office and it takes
about 5 minutes to sync up. I have everything hard-coded so the users
should do fine. I need to figure out a couple other things, but thats
in another post. Thanks everyone for all the help!
Eric Krauss
"Larry Linson" <larry....@ntpcug.org> wrote in message news:<cCSd7.805$PN3.2...@paloalto-snr1.gtei.net>...
David W. Fenton
>I don't understand why working with local data would be preferable
>unless the remote location was running disconnected from the wire
>most of the time. . . .
This is the standard replication scenario, yes, especially where
the cost of having a constant connection would be very high, and
the bandwidth not sufficient to adequately support Access.
> . . . We find a centralized database with up to the second data
>to be very valuable. . . .
It's entirely a cost/benefit analysis. If your operation requires
up-to-the-minute data, then, obviously, replication is not going to
work. If you can get by with data that's not always in synch, then
you have to determine the costs for having various levels of "in
synchness." In 1998 when my NYC client opened their London office,
there was *no* other alternative that would not have cost upwards
of $25-50K in the first year. Really. With Access, we did for $14K,
including development and deployment of the whole app, and a trip
to London for me!
> . . . I suppose it depends on what you are doing
>whether this is relevant. Our Citrix performance (not PCA) is
>actually better than some of our stand alone systems due to the
>old workstations involved.
Replication is not appropriate for all scenarios.
But I don't think people consider it in many situations where it is
actually made to order for the problem at hand.
David W. Fenton
>I use PCAnywhere and VNC over DSL<->Internet(PPTP)<->DSL with
>384K/384K at the server end, and 768/256K at the client end, and
>can barely discern the lag under normal conditions. It gets slow
>when a link is having technical problems sometimes.
Huh. I use VNC on my 100BaseT LAN to administer my NT box, and it's
pretty damned slow. I could never work with it, but it's certainly
preferable to standing up to use the keyboard and mouse way over
there, or spending money for one of those expensive/unreliable
switch boxes.
Steve Jorgensen
run at a pretty high priority. For some reason, this makes a
significant difference even on 56K connections where you would think
that the network is the sole meaningful bottleneck.
When I use VNC with a Windows 2000 host, I have to change the settings
on the host computer so background processes run with a higher
priority. I have yet to find a way to increase the priority of VNC
alone.
Dale Geffs
and I completely agree it depends on the situation, budget etc. as to
whether it makes sense. I had such bad luck I devised my own
replication scheme for one application that used exported files and
email to transmit them. Of course this was prone to all sorts of
problems too.
People were constantly complaining that data they just entered didn't
show up across the country (duh!!!) and yet they wanted the ability to
disconnect from the network and work with the data at home or on the
road. Perhaps this was so long ago I was working with Access 95 and
that may have been my replication problem. I trust you are using 97,
right?
David W. Fenton
>At least it's good to know someone is having success with
>replication and I completely agree it depends on the situation,
>budget etc. as to whether it makes sense. I had such bad luck I
>devised my own replication scheme for one application that used
>exported files and email to transmit them. Of course this was
>prone to all sorts of problems too.
Well, if you need to rely on email to synch, then, yes, you have to
roll your own. Jet replication cannot support that without risking
the integrity of your entire data set.
>People were constantly complaining that data they just entered
>didn't show up across the country (duh!!!) and yet they wanted the
>ability to disconnect from the network and work with the data at
>home or on the road. Perhaps this was so long ago I was working
>with Access 95 and that may have been my replication problem. I
>trust you are using 97, right?
I use A97. I never used replication until I started with A97 (I did
only one app in A95). Replication is vastly improved in Jet 4. It's
one of the few truly bright spots in Access 2000. Unfortunately for
my clients, the problems are so great as to outweight the
advantages.
Replication is tricky, and the documentation is mostly terrible,
and Replication Manager has to be the worst-designed, most counter
-intuitive user interface ever created, so it's just that much
harder to figure out how it works. I highly recommend hanging out
in
microsoft.public.access.MichaelKaplan^H^H^H^H^H^H^H^H^H^H^H^H^Hrepli
cation if you're ever contemplating the possibility of diving in.
It is really quite worth it, in the end.