Access with SQL Server

[Big Time]

I'm aware that Access is best used for small to medium size enterprizes.
What I'm interested in is the use of Access as a backend when it's managed
by SQL Server on the web. I currently have an Access2K db (approx 80,000
records) where I'm the only user. The db resides on my network share. I'm
looking to put the DB online where it will be have approximately 30 users
who will have access to updating data. In general, there will be no more
than 5-10 people updating at any given time and this will all be done
through the web. Furthermore, the DB will be accessed via the web
approximately 1000 times per day to read data. When using SQL server to
manage the system, will an Access DB be too slow to handle this type of
situation? I"m debating whether I should upsize the DB or not. If SQL
Server can manage this type of data efficiently, I'd probably just connect
the DB via an ODBC connection. However if it's required, I can upsize the
DB although this might require some re-development.

Any thoughts or shared experiences is much appreciated.

Cheers,

Mike

[Rich P]

If more than one person hits an Access mdb from the web, it will die.
Access is a formidable Desktop tool (Desktop) and a great training tool
for web development (training wheels). For anything that is different
than Desktop operations or training, like an Enterprise situation (lots
of people hitting your DB and lots of transaction processing), you need
Sql Server. It's not just the size of the data to consider but how many
people will be accessing the DB AT THE SAME TIME and how much
transaction processing will be going on AT THE SAME TIME. Ideally,
Access is a single user tool, although it seems to be able to handle a
few people simultaneously on a LAN. The web is a whole different
environment because now Access would be serving a Web Server, not just a
few users. The web server is what would kill Access. Fight fire with
fire (Web Server with Sql SERVER).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Nicole]

> What I'm interested in is the use of Access as a backend when it's managed

by SQL Server on the web. When using SQL server to
manage the system, will an Access DB be too slow to handle this type of
situation? I"m debating whether I should upsize the DB or not. If SQL
Server can manage this type of data efficiently, I'd probably just connect
the DB via an ODBC connection. However if it's required, I can upsize the
DB although this might require some re-development.

Can you please clarify? If Access is the "backend," how is it being
managed by SQL Server? Perhaps you misspoke and meant that Access was
the front end internally, with SQL Server as the backend housing the
actual data?

I would program your web site to interface directly to the SQL Server
data. You can then still use the existing Access front end internally
for data entry, etc., if you desire.

I would not use Access as a web server while connecting to a SQL
Server for data. It just seems like an extraneous bottleneck.

[Rich P]

First, let me correct my previous statement about the causes of death of
Access. My personal experience (acc97) has been that if a few people
hit it at the same time from the web, the web server will overload
Access and Acccess will lock/freeze up. Maybe it was the web server
(IIS4).

As for using Access as a front end to Sql Server for data entry, that is
actually a standard procedure, and exactly what Access was designed for
- to be a front end. It is just unclear about what you are referring to
between Access, the web, and Sql Server. If you want to do data entry
from a web page, then just enter the data directly into sql Server from
the page (asp, aspx). Asp(x) is also a front end to Sql Server, just
through a web server. If you are looking to do data entry to sql server
from the web, Access would nevercome into the picture. However, if you
will be having people enter data to sql server through Access at the
office, and then present this data on the web through asp(x), well, that
is completely doable. Again, that would be the standard way to do it.

If you are now looking for advisement on how to enter data to sql server
from Access, this ng would be a good resource for that. If you need
advisement on how to enter data into sql server from the web, most of
the seasoned Access programmers can advise you on this at this NG, but
you might get a quicker response at an asp NG.

Is there any kind of programming technique you need advisement on?

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!