Do users need to be able to access server that back-end db is on?

[worksfire1]

I have 650 users located all across the U.S. that are out in the field and
log on to laptops throughout the day. Does my company have to have a server
out there that all 650 users can access the back-end database that contains
the tables that each of their front-ends needs?

 

[John W. Vinson]

I have 650 users located all across the U.S. that are out in the field and
log on to laptops throughout the day. Does my company have to have a server
out there that all 650 users can access the back-end database that contains
the tables that each of their front-ends needs?

If you are trying to use an Access frontend/backend linked database design
over the Internet, you're cruising for trouble!! Access is VERY dependent on a
very fast, very stable network connection - the internet is neither.

You should really consider a "terminal server" connection using something like
Citrix/Server or Windows Terminal Server; the laptops would connect to an
individual's private directory containing a frontend .mde file, linked to the
backend on the same corporate LAN.

Alternatively, you may want to make the (considerable) effort of converting
the application to a Web interface and have the remote users connect to the
webpage. A SQL or other client server backend would be preferable in this
case.


John W. Vinson [MVP]

 

[worksfire1]

The users would VPN into the company's network. Would that take care of the
issue with having a fast, stable connection to the internet that you
mentioned?

They would then be able to open a shortcut to the front-end database. Does
the back-end database have to be on one single central server so that all 650
users would be using the same back-end database?

We can convert the back-end to SQL if needed and use SQL Server, how doees
this help?

 

[John W. Vinson]

The users would VPN into the company's network. Would that take care of the
issue with having a fast, stable connection to the internet that you
mentioned?

Yes, just so long as both the Access frontend and the backend database are on
the corporate network side. With VPN the user's computer should be acting just
as a terminal - the only thing sent "down the wire" is screen updates, not
Access queries.

They would then be able to open a shortcut to the front-end database. Does
the back-end database have to be on one single central server so that all 650
users would be using the same back-end database?

If the users need to share the same data, then of course they need to share
the same database. That's where the data IS.

We can convert the back-end to SQL if needed and use SQL Server, how doees
this help?

JET can handle nominally up to 255 users; in practice I'd be uncomfortable
with much over 100 concurrently updating users, and even that will need
well-designed and tested forms and queries (e.g. forms which open only a
single record rather than the entire table, optimized indexes, etc.) Even at
that, performance can suffer, and MDB backends are indeed prone to corruption.

SQL is considerably more robust in terms of multiple user access; it allows
"hot backups", transaction logging, security and size of database.
Business-critical data (How upset will the Big Boss get if the database is
down for an hour? How upset will people be if the database is destroyed by
corruption and needs to be restored from last week's backup?) should be stored
in SQL, probably not in JET.

I think your application would be much better stored in SQL; Access can make a
very good frontend to the data stored there.

John W. Vinson [MVP]

 

[worksfire1]

John,
Thank you for the wealth of information. I will definitely avidly pursue
using a SQL Server back-end for the many reasons you outlines. Thank you.