Access across the WAN

[Robert]

I have set up an Access database that I would like to have available to edit
across the WAN. I have tried it and it is very slow and cumbersom. Does
splitting the DB help with speed across the WAN? Or is there some other way
to improve performance when working with Access at multiple sites?

 

[Sylvain Lafontaine]

Splitting will change nothing to the speed (however, it's still a very good
idea to do so if you want to go multi-users).

For your speed problem, see
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[TonyT]

Hi Robert,

Splitting the database into front and back ends will improve performance,
but many other things also come into play, like how close the directory is to
the route drive for the _be and it is always worth having a permanently
*connected* form open on each front end so it doesn't have to establish a
connection each time a user tries to open a form.

See http://www.granite.ab.ca/access/performancefaq.htm for more info.

hope this helps,

TonyT..

 

[Sylvain Lafontaine]

Oops, I've made a small error here: splitting the file will slightly
increase the performance because the forms, reports and modules won't have
to be transferred over the wire. However, the (lack of) speed for accessing
the data will remain the same.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Splitting will change nothing to the speed (however, it's still a very
good idea to do so if you want to go multi-users).

For your speed problem, see
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Robert]

Most of the WAN traffic would not be 'write' activity, but read only via Data
Access Page. Most likely it would be only 3 users writing data. Would you
still recommend an alternate solution in that situation?

 

[Robert]

Thanks for the input.
--
R. Shigley
Thermadyne

Oops, I've made a small error here: splitting the file will slightly
increase the performance because the forms, reports and modules won't have
to be transferred over the wire. However, the (lack of) speed for accessing
the data will remain the same.

 

[Robert]

Thanks for the input.
--
R. Shigley
Thermadyne

Hi Robert,

Splitting the database into front and back ends will improve performance,
but many other things also come into play, like how close the directory is to
the route drive for the _be and it is always worth having a permanently
*connected* form open on each front end so it doesn't have to establish a
connection each time a user tries to open a form.

See http://www.granite.ab.ca/access/performancefaq.htm for more info.

hope this helps,

TonyT..

 

[Douglas J. Steele]

Yes. Access databases are very sensitive to network problems (they've been
likened to the miner's canary for networks!).

 

[Larry Linson]

Actually, I wouldn't recommend DAPs, either. They are "deprecated" in Access
2007... it will support running the ones you already have, but you can't
develop new ones or maintain existing ones (for maintenance, you'll need to
maintain a copy of Access 2003 or earlier). They had limitations that kept
them from gaining "earthshaking acceptance."

WAN or Internet connections just are not adequate for a file-server database
such as Jet. Access uses the remote DB file just as it would a file on a
local hard drive; that is, it doesn't pull everything across the network,
but it pulls a LOT across the network and does all the data extraction,
manipulation, etc. on the user's machine.

There are solutions you can use with a WAN... such as Microsoft Terminal
Server, or a genuine web-server based application done in classic ASP or
ASP.NET, and some steps in between.

Larry Linson
Microsoft Access MVP

 

[Tony Toews]

There are solutions you can use with a WAN... such as Microsoft Terminal
Server, or a genuine web-server based application done in classic ASP or
ASP.NET, and some steps in between.

Or using SQL Server as the data store. Tom Ellison once stated in his
testing he got adequate results on a dialup connection.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[David W. Fenton]

Actually, I wouldn't recommend DAPs, either. They are "deprecated"
in Access 2007... it will support running the ones you already
have, but you can't develop new ones or maintain existing ones
(for maintenance, you'll need to maintain a copy of Access 2003 or
earlier). They had limitations that kept them from gaining
"earthshaking acceptance."

Is this true? I know many people are wrongly claiming that you can't
create an A2K3 MDB in A2K7, but this is not true. Replication and
user-level security are supported in A2K7 if you use an MDB.

Are you certain DAPs cannot be created or edited at all in A2K7?

 

[David W. Fenton]

Or using SQL Server as the data store. Tom Ellison once stated in
his testing he got adequate results on a dialup connection.

But Windoes Terminal Server is by far the *easiest* solution for a
WAN.

 

[Robert]

Thanks for the input. I don't have the luxury of purchasing other solutions.
We have been using Excel as a DB which is miserable. How about the Replica
feature. Could I replicate the Access DB on two servers and just syncronize
daily?

 

[Douglas J. Steele]

Replication might be an answer.

Be prepared for a bit of a learning curve, though: while not as complicated
as, say, Access User-Level Security, Replication is still a bit of work.

 

[Larry Linson]

Or using SQL Server as the data store. Tom Ellison once stated in his
testing he got adequate results on a dialup connection.

Then Tom's patience must exceed mine and that of my users!

I worked on a project with some remote users on a WAN attaching to an
Informix database via a 256KB leased line. I feel certain that they
periodically had sweep the facilities to remove the remains of those users
who had died of boredom waiting for response. There was a significant, and
very believable, increase in the users' happiness when their employer
upgraded the WAN to a T-1 connection.

I just can't imagine "adequate" performance at 28KB, or 56KB which in the US
is limited to actually 53KB, (or even 128KB ISDN) after hearing the moans
and groans from those users.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

Is this true? I know many people are wrongly claiming that you can't
create an A2K3 MDB in A2K7, but this is not true. Replication and
user-level security are supported in A2K7 if you use an MDB.

Are you certain DAPs cannot be created or edited at all in A2K7?

It was publicly blogged by the development team, but I don't have a link to
that blog entry. I was somewhat disappointed, because some things Lyle had
posted had made me think this might be used to distribute Access
applications, not as web-based, but just to users who didn't have Access, as
an alternative to the runtime, for simple applications.

There's always the possibility that I might have misunderstood, but if so, I
was definitely not the only one, and there might have been a later change in
plans, of course. In a different megacorp, I have seen a product release
cancelled the day before it was scheduled to be announced.

Larry Linson
Microsoft Access MVP

 

[Brendan Reynolds]

It is true that you cannot create new DAPs or edit existing DAPs in Access
2007 Beta 2 TR. I haven't installed the final release version yet, but I
think that it is *extremely* unlikely that this will change.

 

[David W. Fenton]

It was publicly blogged by the development team, but I don't have
a link to that blog entry.

There was substantial misunderstanding of what was going on, as is
clarified by Eric Rucker in comments on the last Sharepoint post.
Some were claiming that replication and user-level security was not
supported at all, but it *is* supported with an MDB, and is editable
with A2K7.

I was somewhat disappointed, because some things Lyle had
posted had made me think this might be used to distribute Access
applications, not as web-based, but just to users who didn't have
Access, as an alternative to the runtime, for simple applications.

There's always the possibility that I might have misunderstood,
but if so, I was definitely not the only one, and there might have
been a later change in plans, of course. In a different megacorp,
I have seen a product release cancelled the day before it was
scheduled to be announced.

Given the lack of understanding regarding replication and user-level
security, I have to wonder if the DAP situation has been
misunderstood, as well. I can't see why they'd support A2K3-format
MDBs for replication and ULS but *not* support A2K3-format DAPs.

 

[David W. Fenton]

I don't have the luxury of purchasing other solutions.
We have been using Excel as a DB which is miserable. How about the
Replica feature. Could I replicate the Access DB on two servers
and just syncronize daily?

Replication is never a solution for performance or reliability
problems. If you tried direct replication over a WAN connection,
you'd be in exactly the same boat as with trying to run the Access
app, and you'd be in danger of corrupting one or both of your
replicas.

Indirect replication works great on a WAN (it works great over
*dialup*), but it's hard to set up and keep running reliably.

The real solution to WAN scenarios is Windows Terminal Server. I
have 10 years of experience with replication and would never
recommend anything *but* WTS for a WAN environment.

 

[David W. Fenton]

Replication might be an answer.

Replication is not a solution for a WAN unless you embark on
indirect replication.

Be prepared for a bit of a learning curve, though: while not as
complicated as, say, Access User-Level Security, Replication is
still a bit of work.

Replication is *substantially* more complicated than ULS. Anyone
who'd ever used it in a non-trivial scenario (i.e., not with just
direct repliation over a LAN) would knmow that.