Is this feasible?

[John]

Hi

We have an access desktop app with front-end/back-end situation where all
tables are in the back end and everything else, forms/queries, are in the
front end. We would like to make a web app to use the same access database
but are worried about access being able to handle web app users. Is it
feasible to move the access backend tables to the SQL Server and link the
sql server tables in the access front end? It will not help access desktop
app as all processing will still be done by access but the web app can
presumably benefit from tables being on the SQL Server? Then over time we
can also re-write the access desktop app to be native sql.

Thanks

Regards

 

[William Morris]

Hiya John,

The LAST thing you want to do is put Access as the backend of anything with
more than one or two users. Put your data in SQL Server.

 

[J Young]

Put all your data in SQL Server (MSDE) and use stored procedures and views
for extracting data and use Access strictly for the front end of the
application.

Jim

 

[Larry Linson]

The LAST thing you want to do
is put Access as the backend of
anything with more than one or two
users. Put your data in SQL Server.

Where did you ever get that idea?

There are many factors that enter into multiuser access to Jet databasese,
including the requirements, design, and implementation of the application,
and the hardware, software, and network environments. If all those factors
are near-perfect, we have reliable reports of Access supporting 100+ happy,
concurrent users. Even if all are not near-perfect, we routinely see reports
of split Access-Jet databases supporting 30 to 70 users. We've had whines in
the past about "Access falling over with four users" and, any that we could
get details on turned out that all the factors were about as far from
perfect as possible, but the primary culprit was design by someone who
didn't know what they were doing.

Your user estimate is obviously nearly as low as it could possibly be,
unless some dunderhead claimed Access wouldn't support _any_ users.

And, by the way, "native SQL" does not have a desktop UI capability, just so
you won't make that mistake again.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

What you describe is certainly possible. Access makes a good client
application for server databases on the same LAN or WAN. If the person
needing the rich-client-interface is, however, accessing across the
Internet, you'll want to run Access on the server via some sort of Remote
Access Software (for one user, something like pcAnywhere or ReachOut would
be fine; for multiple users, take a look at Virtual Private Network and
Windows Terminal Server / Citrix).

However, Access itself would not be involved in supporting the web users in
such a situation. You have a Jet database, and you'd access it with either
DAO, or more likely, ADO code from .asp pages. If the database was on the
same machine, you can almost certainly support more concurrent users than
you could with an Access-Jet split database on a LAN. A Jet database can be
quite adequate for a web site with "modest" traffic. See my response to
William Morris for some numbers on concurrent users.

The advice you have received suggesting that you _need_ to convert to SQL
Server, without any indication of the requirements, or the expected number
of concurrent users, is "hasty", to say the least.

 

[satheeshks]

We encountered a similar scenario before. Yes, you can upsize (use the
upsizing utility) the MS-Access database tables to a SQL server and link
them back in the MS-Access database. You may want to check the following in
your front end. (1) AutoNumber (identity) (2) All the queries. There are
some flavors of MS-Access query may not work well with linked SQL server
tables. You may also have to tweak your front end to avoid any performance
issues. You may also want to consider the effort required to do the above
before making the final decision.