Front End / Back End Questions

#1 (**permalink**) 28-Jul-2006, 08:39 AM

All:

I have a question on this setup. I created a front end / back end
database and deployed the front end to some users for testing. The
backend was on the server where I reside. The users were in other sites
and stated the reponse time was a killer and everything ran very slow.
When a put a local copy of the whole database on their server (not
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13755-front-end-back-end-questions.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
split), they said it ran fast. What is the best option for me here? All
users in 4 different sites must have access to the same tables for data
entry purposes (updates). I really don't want to do replication, bt
will consider it. Any ideas?

Mike

#2 (**permalink**) 28-Jul-2006, 08:39 AM

iMike12345 wrote:
> All:
>
> I have a question on this setup. I created a front end / back end
> database and deployed the front end to some users for testing. The
> backend was on the server where I reside. The users were in other
> sites and stated the reponse time was a killer and everything ran
> very slow. When a put a local copy of the whole database on their
> server (not split), they said it ran fast. What is the best option
> for me here? All users in 4 different sites must have access to the
> same tables for data entry purposes (updates). I really don't want to
> do replication, bt will consider it. Any ideas?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
>
> Mike

Access does not like working over a marginal LAN. I would not recommend
it for use on a WAN. Could that be the problem?

--
Joseph Meehan

Dia duit

#3 (**permalink**) 28-Jul-2006, 08:39 AM

"iMike12345" wrote

> . . . I created a front end / back end
> database and deployed the front end
> to some users for testing. The backend
> was on the server where I reside. The
> users were in other sites and stated the
> reponse time was a killer and everything
> ran very slow. When a put a local copy
> of the whole database on their server (not
> split), they said it ran fast. What is the
> best option for me here? All users in 4
> different sites must have access to the
> same tables for data entry purposes
> (updates). I really don't want to do
> replication, bt will consider it. Any
> ideas?

Joseph is correct that a split Access - Jet database application on a WAN or
even an old, slow LAN requires Very Patient Users (and those, in my
experience are rare). The reason for this is the way Access (or any
file-server database) works... the remote backend simply is a data
repository, and all the data that would normally be retrieved from the local
hard drive has to pass across the network.

(NOTE: That does not, repeat NOT, say that "the entire database has to be
fetched across the network"... careful design and implementation, making use
of indexes and limiting data retrieved, can significantly speed up
operations. But, it is difficult or impossible to do enough to make
performance across a WAN or slow LAN acceptable.).

Replication is "not for the faint of heart" and I recommend it only when
there is no other option... as when you have a database back at the office,
but people having to update off-site on their unconnected notebook
computers.

One option, and likely the simplest (but some expense for Client Access
Licenses) would be to use Microsoft's Terminal Services, so that the user,
from their own machine, is actually executing the database on the server.
You'll need the cooperation of your system administrator to set this up. I
believe MVP Albert Kallal has some good information on this at
http://www.members.shaw.ca/AlbertKallal.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755

Another option, more time and effort (probably costing more than the TS
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
CALs), would be to convert your application so that it has Access client
applications for the users, and a true server database for the backend (MS
SQL Server, which could be the free MSDE that comes with Access; or, for
that matter, any ODBC-compliant server database). My guess is that you can
easily support 20 or more concurrent users with MSDE, but for more, you
should consider a more robust server.

Larry Linson
Microsoft Access MVP

#4 (**permalink**) 28-Jul-2006, 08:40 AM

Re running multiple users over a slow LAN or WAN. Larry's answer is correct,
however there is another method, run two systems (ie copy of database on
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
remote system) and collect copies of all transactions (ie appends, updates
and deletes) then transmit these over your slow link (they are fairly small)
and run them on the local system.

This is similar to what replication does and is not as good a solution as
using SQL server as your back end. However depending on the nature of your
database and the transacrtions that it performs, it can be a viable solution.

This solution demands regular coopying of the databases. to ensure that the
odd transaction is not lost.

Peter the grate.

"Larry Linson" wrote:

> "iMike12345" wrote
>
> > . . . I created a front end / back end
> > database and deployed the front end
> > to some users for testing. The backend
> > was on the server where I reside. The
> > users were in other sites and stated the
> > reponse time was a killer and everything
> > ran very slow. When a put a local copy
> > of the whole database on their server (not
> > split), they said it ran fast. What is the
> > best option for me here? All users in 4
> > different sites must have access to the
> > same tables for data entry purposes
> > (updates). I really don't want to do
> > replication, bt will consider it. Any
> > ideas?
>
> Joseph is correct that a split Access - Jet database application on a WAN or
> even an old, slow LAN requires Very Patient Users (and those, in my
> experience are rare). The reason for this is the way Access (or any
> file-server database) works... the remote backend simply is a data
> repository, and all the data that would normally be retrieved from the local
> hard drive has to pass across the network.
>
> (NOTE: That does not, repeat NOT, say that "the entire database has to be
> fetched across the network"... careful design and implementation, making use
> of indexes and limiting data retrieved, can significantly speed up
> operations. But, it is difficult or impossible to do enough to make
> performance across a WAN or slow LAN acceptable.).
>
> Replication is "not for the faint of heart" and I recommend it only when
> there is no other option... as when you have a database back at the office,
> but people having to update off-site on their unconnected notebook
> computers.
>
> One option, and likely the simplest (but some expense for Client Access
> Licenses) would be to use Microsoft's Terminal Services, so that the user,
> from their own machine, is actually executing the database on the server.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
> You'll need the cooperation of your system administrator to set this up. I
> believe MVP Albert Kallal has some good information on this at
> http://www.members.shaw.ca/AlbertKallal.
>
>
> Another option, more time and effort (probably costing more than the TS
> CALs), would be to convert your application so that it has Access client
> applications for the users, and a true server database for the backend (MS
> SQL Server, which could be the free MSDE that comes with Access; or, for
> that matter, any ODBC-compliant server database). My guess is that you can
> easily support 20 or more concurrent users with MSDE, but for more, you
> should consider a more robust server.
>
> Larry Linson
> Microsoft Access MVP
>
>
>

#5 (**permalink**) 28-Jul-2006, 08:40 AM

All:

Thanks for your responses. This gives me a good idea of where to go
from here. I talked to the IT guys and they stated they could probably
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755
do a terminal services setup fairly easily. Right now I am copying the
db's over to the remote sites after importing all changes into a
master. Kind of a pain, but it works for now until the TS solution is
up and running.

Thanks all!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13755

~Mike