Can Access handle this configuration, or do I need SQL Server?

[Paul Ponzelli]

Our organization has four offices networked over 1.55 mb T1 lines.

We have about twenty Access 2002 databases with the front ends in the four
offices linked to the back end mdb files in a server at one of the four
locations.

Although we have about 50 users in total at the four offices, we seldom, if
ever, have more than 5 of them working on any one of these databases at a
time, and never as many as 10 at once.

Although improvements and enhancements are never ending, these databases are
functional, and they're getting the job done.

Within each office, performance is not a problem; but over the network
between offices, things go slow. Some forms can take 30 seconds to open.
I'm planning on improving performance by following the guidelines in Access
Help, the MS web site and suggestions from the noble contributors to these
newsgroups by doing such things as indexing query criteria fields and the
like.

My question is this: Given that we have four locations in the WAN, if I take
the proper steps to optimize performance, is Access a feasible RDBMS to use
in such a configuration, or do I need to scale up to MSDE or SQL Server?

I realize the Jet engine is only in Maintenance Mode, and I purposely didn't
post this question in the SQL Server or MSDE newsgroups, because I'm pretty
sure they'll tell me to bite the bullet and upscale now. However, my
colleagues and I would have to go through an immediate learning curve, and
I'd rather delay that day of reckoning if possible.

Again, if I take the steps to optimize performance of my Access mdb files,
should that be workable over the network I've described?

Thanks in advance,

Paul Ponzelli

 

[Douglas J. Steele]

Take a look at what Albert Kallal has to say about using Access over the WAN
at http://www.attcanada.net/~kallal.msn/Wan/Wans.html

 

[Immanuel Sibero]

Doug,

That site has not been working.

Immanuel Sibero

 

[Jeff Conrad]

It has been moved.

Try:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[Immanuel Sibero]

Paul,

My question is this: Given that we have four locations in the WAN, if I take
the proper steps to optimize performance, is Access a feasible RDBMS to use
in such a configuration, or do I need to scale up to MSDE or SQL Server?

No, not feasible. T1 is too slow to run Access across the wire. You should
look at other options such as remote control, terminal service, citrix, or
replication. I would seriously look into Terminal Service.

HTH,
Immanuel Sibero

 

[Immanuel Sibero]

Thanks Jeff,
I was looking for it.

Immanuel Sibero

 

[Sylvain Lafontaine]

Unless it has already been optimized, optimizing your database with proper
indexes and queries has a good chance of filling the bill. However, it's
only you who can make the final judgment on that call and say, for example,
that a waiting time of 15 seconds might be good enough or not.

The use of Terminal Server (TS) / Citrix can also alleviate the problem in
exchange of some $. The use of replication might also be a very good idea
in your case. However, by using Access over T1 lines, you should realize
that you are at the frontier of its limitations.

S. L.

 

[Douglas J. Steele]

Darn! I knew that, and, in fact, had corrected the page in my FGR
(Frequently Given Responses) file just prior to posting it. Unfortunately, I
obviously deleted the wrong URL from there! Thanks to Jeff Conrad for
stepping in.

 

[Paul Ponzelli]

Thanks for the comments and the references, everyone.

Two additional questions:

1. Have any of you used a thin client for a production database over a wide
area network? If so, is it satisfactory?

2. I'm thinking that Replication could be problematic if there are
transmission interruptions over a WAN. I've also heard that Replication is
difficult to implement over a WAN even under the best conditions. Is this
true?

Thanks again in advance

Paul

 

[Immanuel Sibero]

Hi Paul,

1. Have any of you used a thin client for a production database over a wide
area network? If so, is it satisfactory?

Using Access?
I guess we first have to agree on what you mean by "thin client". The
experts will jump in and correct me if I'm wrong, but the traditional Access
Jet/MDB scheme simply can not be implemented as a "thin client" application.
By design, Access (i.e. Jet engine), which is really fat, runs on every
client, so there is no way to make the client thin. This is also the reason
why even a T1 WAN is not sufficient.

Terminal Serice (TS) or Citrix type of implementation is about as close to a
"thin client" as Access Jet/MDB can get. Even in a TS implementation, Access
still runs as a fat client, the Terminal Server is really the Access/Jet
client.

2. I'm thinking that Replication could be problematic if there are
transmission interruptions over a WAN.

Well, if you have transmission interruptions, you would be worse off with
your current configuration (running Access over WAN), than if you're using
replication. Using replication, you would only use the WAN when you're
synchronizing (i.e. once a day, every other day, etc). It doesnt take but a
few minutes to synch. You're only vulnerable to interruptions during
syncrhonization. In contrast, when you run Access over WAN, you're
vulnerable to interruptions during your entire Access session. So I dont see
replication being more problematic than your current set up.

I've also heard that Replication is
difficult to implement over a WAN even under the best conditions. Is this
true?

When properly designed, replication can work very well. You may have heard
it's difficult, but by browsing the replication group, I get the impression
that it is difficult because people do it without full understanding of its
purpose. You would have to browse the replication group for more
information. There are definitely do's and dont's, and additional
maintenance. In your case, I think both replication and TS options are
viable. TS option would probably require an investment of a Terminal Server
machine. Replication requires no additional hardware.


HTH,
Immanuel Sibero

 

[Albert D. Kallal]

1. Have any of you used a thin client for a production database over a
wide area network? If so, is it satisfactory?

Yes, it works well...in fact very well. Users with anything better then a
dial up connection will find the system works very well. With the kind of
connection you are talking about, users will be hard pressed to tell the
difference when running the application locally, or across the country.

One spot of caution area is local printing, as that has to come across the
network.

2. I'm thinking that Replication could be problematic if there are
transmission interruptions over a WAN. I've also heard that Replication
is difficult to implement over a WAN even under the best conditions. Is
this true?

file based replication is more suited to the case where salesman on the road
come back to the actual office, and plug into the actual office network.
And, replication might not be applicable for situations where some users
cannot see other users updates. (only you can answer this kind of question).

Replication can also work very well when you have a true database server on
the back end, but then we are not talking about a ms-access solution
anymore.

Using Terminal Services is the least amount of work by a country mile, as
you don't have to modify your application. Changing, and setting up your
application to work with replication entails a good amount of changes, and
introduces quite a bit of complexity into the system. Further, with a thin
client, then there is MUCH LESS support issues (you don't have to install
ms-access on the remote computers, you don't have to push out updates to
software on remote computes. Those remote computers have NONE of your
corporate data, so security is also better with TS. With TS, you centralize
the setup and running of the application into one spot....not a bunch of
computers all over the place...

 

[kiln]

Hi Paul

I don't think that there is a way to "safely" use Access in it's bound
form, out of the box config...running over a WAN as you are usually
leads to some sort of back end corruption eventually.

Access running on a server, with remote workstations using the app via
Terminal Services, is a very practical arrangment. Terminal Services,
esp the win2003 version, isnt' free, but it is pretty robust and not all
that hard to set up.

You could build an n-tier SQL Server based setup, but that would amount
to a total rewrite of all of your applications. Lots and lots of work.

 

[Paul Ponzelli]

Thanks for your input here, kiln.

I don't think that there is a way to "safely" use Access in it's bound
form, out of the box config...running over a WAN as you are usually
leads to some sort of back end corruption eventually.

I do have a concern about it because I've experienced it. Although not
nearly as much as you'd expect. Maybe once a year, and then on only not mdb
file. With the others it hasn't been a problem (yet).

Access running on a server, with remote workstations using the app via
Terminal Services, is a very practical arrangment. Terminal Services,
esp the win2003 version, isnt' free, but it is pretty robust and not all
that hard to set up.

I'm fascinated by the idea, which hadn't occurred to me before. I've got a
couple of questions in this regard which I'm going to ask Albert in the
adjacent conversation thread.

You could build an n-tier SQL Server based setup, but that would amount
to a total rewrite of all of your applications. Lots and lots of work.

That's what I'm trying to avoid, or at least forestall.

PP

 

[Paul Ponzelli]

Thanks for your responses, Albert.

I just spent some time reading your web pages on solutions to running Access
on a WAN and also the comparison of thin client technology vs. dot net. I'm
very interested by the comments in this discussion suggesting that a thin
client app like Terminal Server might be a good solution for the WAN problem
with Access.

Two more questions in that regard:

1. When several clients use the database concurrently, it would seem the
thin client (let's say TS) server has to provide a separate instance of the
front end to each of those clients. How does it do this? Does it let each
of them click on an icon which opens a separate instance of the app for each
of them?

2. In most of your comments about TC, you seem to view it as a good idea.
But then at the end of your comparison of TC technology with dot net, you
argue that dot net is even better because it provides for interprogram
communication, whereas TC only controls one program at a time. But I don't
think I've grasped the reason for your conclusion. For the most part, my
users need to go into the Access application, enter data, and generate
queries and reports. Why is inter-program communication of concern to them?
If they need to export a query view to say, Excel, can't they perform the
export and then open Excel (just like they opened Access) since they're
controlling a portion of the server, and view the result?

Thanks also for hosting those very interesting web pages.

Paul

 

[Van T. Dinh]

For the first question, Tony Toews offers a free utility "AutoFE" (FE:
Front-End) that you can use in TS/Citrix that automatically create / update
a separate copy of the database for each user and then open this copy.

See Tony's Web site:

http://www.granite.ab.ca/access/autofe.htm

 

[Paul Ponzelli]

For the first question, Tony Toews offers a free utility "AutoFE" (FE:
Front-End) that you can use in TS/Citrix that automatically create /
update
a separate copy of the database for each user and then open this copy.

Thanks for your reply, Van. However, this thread isn't about setting up a
local copy of the front end in a client-server environment (which is what
Tony's solution addresses). This conversation is about using a thin client
app like TS so the client can view and control the front end that's on the
server or the server's local network. The idea is to avoid slow
transmission rates over a wide area network.

Paul

 

[Rob Oldfield]

I've only had a quick look at the utility but think you might be wrong
there. Creation of FE versions in a standard lan is just a case of copying
the file to the relevant locations, in a TS situation it's pretty much the
same, the only difference being that you'll be copying the file to
(something like) several places on the server (which will generally be
mapped to each users' home folder). I'd suggest taking a more detailed look
at the link.

(...or course, you may already have done so and decided that it won't do the
job, and you may be right.)

 

[Rick Brandt]

Thanks for your reply, Van. However, this thread isn't about setting
up a local copy of the front end in a client-server environment
(which is what Tony's solution addresses). This conversation is
about using a thin client app like TS so the client can view and
control the front end that's on the server or the server's local
network. The idea is to avoid slow transmission rates over a wide
area network.
Paul

You would use that as a method to give each user their own copy of the front end
ON the Terminal Server.

 

[Douglas J. Steele]

Thanks for your reply, Van. However, this thread isn't about setting up a
local copy of the front end in a client-server environment (which is what
Tony's solution addresses). This conversation is about using a thin
client app like TS so the client can view and control the front end that's
on the server or the server's local network. The idea is to avoid slow
transmission rates over a wide area network.

Actually, even with TS each user should have his/her own copy of the
front-end, and I believe Tony's utility facilitates that.

 

[Paul Ponzelli]

My apologies, Van.

After reading Rob and Rick's messages, I went back to your link to Tony's
site and read to the end of his article, and discovered that he specifically
addresses the thin client configuration. In addition, he also provides
links to additional pages with further discussion and observations about
using TS/Citrux to render a vittual front end for a remote user.

So thanks for the very helpful suggestion, and to Rob and Rick for making me
realize it.