Architectures for multi-user access database

[premmehrotra]

I currently have a multi-user access database which is put on a shared
drive L: on a Windows Servers. Entire database is one file premdb.mdb.
Users access this database from their laptops. Following problems
occur:
1. Access is way too slow in WAN environment. Server is located in New
Jersey and users are in California and Puerto Rico.
2. Database often becomes corrupt
3. When one user updates some data in the database, other users often
have
to wait for long time before they can access the database ...

I have read numerous articles on internet including Microsoft's web
site. I am looking into following architectures and will like to know
other's opinions:

1. Install Microsoft Access on Windows 2000 server and place Access
database on a local drive of the server (i.e. no shared drive for
Access). Create/assign accounts for users on the server and ask users
to logon to the server using Remote Desk Top and run Access on the
server (instead on Laptop..). I am hoping that performance will be
better because now one is only sending screens (using terminal server)
instead of sending data/forms/reports etc via shared drive L: access.
I am using there are no side effects because of remote desk
top/terminal server.
2. Split the database into front end back end, premdb_fe.mdb
premdb_be.mdb. Keep backend on shared drive L, but keep front end on a
local drive of everyone's hard disk drive. It should reduce network
traffic? Will it also reduce chances of corruption.
3. Is there anyway to keep backend on a local drive of Windows Server
and link back end tables in front end using ODBC. I did not have
success because I kept getting error that ODBC connection are not
allowed between Microsoft Access DB and Microsoft Access DB.

Are there other architectures?
Appreciate any response or pointers.
Thanks a lot,
Prem

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should split the db - that should clear up the corruptions. For
each user put a copy of the front-end in a user folder. Use something
like Terminal Server or Citrix (or your idea for Remote Desktop) to
allow each user to access their folder on the server.

When linking Access tables (back-ends) to Access front-ends you do not
use ODBC connections. Access provides it's own connections - just use
the File > Get External Data > Link Tables... and select "Microsoft
Access (*.mdb; *.mda; *.mde)" from the Combo Box "Files of type:".
Follow the directions & Access will do the rest.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQct4Z4echKqOuFEgEQLL8QCbB0kU+vd9tNFEBJ9urSE+IZ SiStQAoOfW
GjAAeHa9M0js4fhHEg4vzkks
=uF2n
-----END PGP SIGNATURE-----
pr**********@hotmail.com wrote:

I currently have a multi-user access database which is put on a shared
drive L: on a Windows Servers. Entire database is one file premdb.mdb.
Users access this database from their laptops. Following problems
occur:
1. Access is way too slow in WAN environment. Server is located in New
Jersey and users are in California and Puerto Rico.
2. Database often becomes corrupt
3. When one user updates some data in the database, other users often
have
to wait for long time before they can access the database ...

I have read numerous articles on internet including Microsoft's web
site. I am looking into following architectures and will like to know
other's opinions:

1. Install Microsoft Access on Windows 2000 server and place Access
database on a local drive of the server (i.e. no shared drive for
Access). Create/assign accounts for users on the server and ask users
to logon to the server using Remote Desk Top and run Access on the
server (instead on Laptop..). I am hoping that performance will be
better because now one is only sending screens (using terminal server)
instead of sending data/forms/reports etc via shared drive L: access.
I am using there are no side effects because of remote desk
top/terminal server.
2. Split the database into front end back end, premdb_fe.mdb
premdb_be.mdb. Keep backend on shared drive L, but keep front end on a
local drive of everyone's hard disk drive. It should reduce network
traffic? Will it also reduce chances of corruption.
3. Is there anyway to keep backend on a local drive of Windows Server
and link back end tables in front end using ODBC. I did not have
success because I kept getting error that ODBC connection are not
allowed between Microsoft Access DB and Microsoft Access DB.

Are there other architectures?
Appreciate any response or pointers.

[premmehrotra]

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should split the db - that should clear up the corruptions. For
each user put a copy of the front-end in a user folder. Use something like Terminal Server or Citrix (or your idea for Remote Desktop) to
allow each user to access their folder on the server.

When linking Access tables (back-ends) to Access front-ends you do not use ODBC connections. Access provides it's own connections - just use the File > Get External Data > Link Tables... and select "Microsoft
Access (*.mdb; *.mda; *.mde)" from the Combo Box "Files of type:".
Follow the directions & Access will do the rest.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQct4Z4echKqOuFEgEQLL8QCbB0kU+vd9tNFEBJ9urSE+IZ SiStQAoOfW
GjAAeHa9M0js4fhHEg4vzkks
=uF2n
-----END PGP SIGNATURE-----
pr**********@hotmail.com wrote:

I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb.

Users access this database from their laptops. Following problems
occur:
1. Access is way too slow in WAN environment. Server is located in New Jersey and users are in California and Puerto Rico.
2. Database often becomes corrupt
3. When one user updates some data in the database, other users often have
to wait for long time before they can access the database ...

I have read numerous articles on internet including Microsoft's web
site. I am looking into following architectures and will like to know other's opinions:

1. Install Microsoft Access on Windows 2000 server and place Access
database on a local drive of the server (i.e. no shared drive for
Access). Create/assign accounts for users on the server and ask users to logon to the server using Remote Desk Top and run Access on the
server (instead on Laptop..). I am hoping that performance will be
better because now one is only sending screens (using terminal server) instead of sending data/forms/reports etc via shared drive L: access. I am using there are no side effects because of remote desk
top/terminal server.
2. Split the database into front end back end, premdb_fe.mdb
premdb_be.mdb. Keep backend on shared drive L, but keep front end on a local drive of everyone's hard disk drive. It should reduce network
traffic? Will it also reduce chances of corruption.
3. Is there anyway to keep backend on a local drive of Windows Server and link back end tables in front end using ODBC. I did not have
success because I kept getting error that ODBC connection are not
allowed between Microsoft Access DB and Microsoft Access DB.

Are there other architectures?
Appreciate any response or pointers.

Thanks, I am assuming that there are no known problems running MS
Access in terminal server environment.

[Larry Linson]

There's just no way to run Access on the user machine across the WAN and get
satisfactory performance with the data on a remote server. Terminal server
is the only satisfactory approach that I know for running the
all-Access-and-Jet multiuser across a WAN.

Otherwise, you could create an Access client for the users and employ ODBC
to access a server database such as MS SQL Server running on the server...
if there are few enough users, the free MSDE (aka Desktop SQL Server) would
suffice for the server backend.

<pr**********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should split the db - that should clear up the corruptions. For
each user put a copy of the front-end in a user folder. Use

something

like Terminal Server or Citrix (or your idea for Remote Desktop) to
allow each user to access their folder on the server.

When linking Access tables (back-ends) to Access front-ends you do

not

use ODBC connections. Access provides it's own connections - just

use

the File > Get External Data > Link Tables... and select "Microsoft
Access (*.mdb; *.mda; *.mde)" from the Combo Box "Files of type:".
Follow the directions & Access will do the rest.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQct4Z4echKqOuFEgEQLL8QCbB0kU+vd9tNFEBJ9urSE+IZ SiStQAoOfW
GjAAeHa9M0js4fhHEg4vzkks
=uF2n
-----END PGP SIGNATURE-----
pr**********@hotmail.com wrote:

I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb.

Users access this database from their laptops. Following problems
occur:
1. Access is way too slow in WAN environment. Server is located in New Jersey and users are in California and Puerto Rico.
2. Database often becomes corrupt
3. When one user updates some data in the database, other users often have
to wait for long time before they can access the database ...

I have read numerous articles on internet including Microsoft's web
site. I am looking into following architectures and will like to know other's opinions:

1. Install Microsoft Access on Windows 2000 server and place Access
database on a local drive of the server (i.e. no shared drive for
Access). Create/assign accounts for users on the server and ask users to logon to the server using Remote Desk Top and run Access on the
server (instead on Laptop..). I am hoping that performance will be
better because now one is only sending screens (using terminal server) instead of sending data/forms/reports etc via shared drive L: access. I am using there are no side effects because of remote desk
top/terminal server.
2. Split the database into front end back end, premdb_fe.mdb
premdb_be.mdb. Keep backend on shared drive L, but keep front end on a local drive of everyone's hard disk drive. It should reduce network
traffic? Will it also reduce chances of corruption.
3. Is there anyway to keep backend on a local drive of Windows Server and link back end tables in front end using ODBC. I did not have
success because I kept getting error that ODBC connection are not
allowed between Microsoft Access DB and Microsoft Access DB.

Are there other architectures?
Appreciate any response or pointers.

Thanks, I am assuming that there are no known problems running MS
Access in terminal server environment.

[premmehrotra]

Larry Linson wrote:

There's just no way to run Access on the user machine across the WAN and get satisfactory performance with the data on a remote server. Terminal server is the only satisfactory approach that I know for running the
all-Access-and-Jet multiuser across a WAN.

Otherwise, you could create an Access client for the users and employ ODBC to access a server database such as MS SQL Server running on the server... if there are few enough users, the free MSDE (aka Desktop SQL Server) would suffice for the server backend.

<pr**********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should split the db - that should clear up the corruptions. For each user put a copy of the front-end in a user folder. Use

something

like Terminal Server or Citrix (or your idea for Remote Desktop) to allow each user to access their folder on the server.

When linking Access tables (back-ends) to Access front-ends you do

not

use ODBC connections. Access provides it's own connections -

just use

the File > Get External Data > Link Tables... and select

"Microsoft Access (*.mdb; *.mda; *.mde)" from the Combo Box "Files of type:". Follow the directions & Access will do the rest.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQct4Z4echKqOuFEgEQLL8QCbB0kU+vd9tNFEBJ9urSE+IZ SiStQAoOfW
GjAAeHa9M0js4fhHEg4vzkks
=uF2n
-----END PGP SIGNATURE-----
pr**********@hotmail.com wrote:
> I currently have a multi-user access database which is put on a

shared

> drive L: on a Windows Servers. Entire database is one file

premdb.mdb.

>
>
> Users access this database from their laptops. Following problems > occur:
> 1. Access is way too slow in WAN environment. Server is located in New

> Jersey and users are in California and Puerto Rico.
> 2. Database often becomes corrupt
> 3. When one user updates some data in the database, other users

often

> have
> to wait for long time before they can access the database ...
>
> I have read numerous articles on internet including Microsoft's

web > site. I am looking into following architectures and will like to know

> other's opinions:
>
> 1. Install Microsoft Access on Windows 2000 server and place

Access > database on a local drive of the server (i.e. no shared drive for > Access). Create/assign accounts for users on the server and ask users

> to logon to the server using Remote Desk Top and run Access on

the > server (instead on Laptop..). I am hoping that performance will be > better because now one is only sending screens (using terminal

server)

> instead of sending data/forms/reports etc via shared drive L:

access.

> I am using there are no side effects because of remote desk
> top/terminal server.
> 2. Split the database into front end back end, premdb_fe.mdb
> premdb_be.mdb. Keep backend on shared drive L, but keep front end on a

> local drive of everyone's hard disk drive. It should reduce

network > traffic? Will it also reduce chances of corruption.
> 3. Is there anyway to keep backend on a local drive of Windows

Server

> and link back end tables in front end using ODBC. I did not have > success because I kept getting error that ODBC connection are not > allowed between Microsoft Access DB and Microsoft Access DB.
>
> Are there other architectures?
> Appreciate any response or pointers.

Thanks, I am assuming that there are no known problems running MS
Access in terminal server environment.

Thanks a lot. I did not know of the free version of Desktop SQL SERVER.
I will take a look,
Prem

[Larry Linson]

<pr**********@hotmail.com> wrote

Thanks a lot. I did not know of the
free version of Desktop SQL SERVER.

It comes on your Access or Office Pro install CDs, but before you get very
excited about it, know that it is deliberately limited in the number of
users it will support. When more than five of its internal "batch processes"
are in progress, it inserts delays. Some have reported adequate response
with up to 25 users on a LAN (not a WAN), but we don't know specifics of
those applications.

The beta of the Express Version of the next release of SQL Server, we hear,
does not have that same limiting mechanism, but it is only in beta test form
and may or may not be free once it is released. On the other hand, it
doesn't make sense to me that Microsoft would release a free version of SQL
Server capable of competing with the for-fee version, now or later.

Larry Linson
Microsoft Access MVP