| Eric Innocently Asks: Can anybody give me the MS link where is stated that Access can handle 255
connections please? I can't find it anymore Luis Provides:http://msdn.microsoft.com/library/backgrnd/html/acmsdeop.htm
Ian Sheds Some Light: I can tell you from experience that Access cannot handle 255 concurrent
connections. I don't care what MS says. We had a database with multiple
tables. The main table where most of the data was retrieved from had about
5000 rows. After 7-8 concurrent connections we began to see serious problems
with performance if not outright ASP crashes. 5000 rows and 7-8 concurrent
connections is not a lot. We basically had about 1000-2000 user sessions a
day, also not a whole lot. We had to move the site up to SQLServer 7.0 and
it works like a charm. If you are expecting 255 concurrent connections to
your database DO NOT USE Access. If you do you will immediately need to redo
your site to work with SQLServer or some other equally powerful data server.
You have been warned.
Dave Adds: Access was never designed to be a database server. It's a great
program for desktop database work, but on a practical basis, it is
entirely unsuitable for ANY task where there will be more than a half a
dozen (if that) concurrent connections on a regular basis. There are a
whole host of issues that come into play with locking that make it
unsuitable as a database server. MS isn't entirely forthcoming with the
limitations of Access, but they do exist. In addition, the stress of using Jet (the access database engine) on the
same box as IIS will slow down the entire web site. And to the person (Sam?) who was asked to back this fact up with
documentation...It sounds like a management type person is
questioning your technical knowledge. Probably because they don't
want to (can't) spring for a SQL server installation. Just tell them that you have to pay if you want to play. Alternatively,
you could look into MySQL which isn't a bad solution, but if you are
developing db apps on the web using ASP, SQL server is the way to
go. If they insist on having you use Access, tell them that you will do
your best, but the results will not be acceptable. Not only is it robust enough to handle the traffic, but by moving to
SQL Server, you can start dealing with access control (users &
groups), logging, and moving your apps into a two-tier (or even n-tier)
structure by using stored procedures. Just my 2 cents.
Michael Gets Technical: Access is Apartment-Single Threaded... (single use), multiple access is qued. SQL Server is Apartment + Free Threaded, multiple uses at one time. Access is fine for 1-10 users depending on the load, and size of the database.
For more than that, a true database is better. (there are lesser expense, or free databases out there, that will perform better than an access tablespace)
Ken Adds: You can download the Kevin Collins Whitepaper on Microsoft Jet Locking from
here: http://support.microsoft.com/support/kb/articles/Q176/6/70.ASP 256 connections are possible - but I wouldn't expect to see that possible in
any kind of real world scenario - each connection would have to be doing
something on a different table, so as not to generate any exclusive
non-shared locks...
A Frightened Danny Responds: You guys are scaring me! We are using Access on a W2K shared server We have not had any problems as yet but usage at this stage is pretty light. It is certainly likely that we will have around 100 users concurrently
accessing the database. We are a non profit organisation and cannot possibly afford the thousands of
£'s that SQL server must cost. Is it possible to work with Access 2000 on our development machines and use
SQL server on the remote server?
Marcos Offers This Advice: Danny, In your case, a MySQL with Linux will really help you move away from the
access problem, I have too experience the problem wih Access 97 and 2000 , they are good up
to a certain degree. But I will be against any "production level" environment using Access
either for a web app or regular fat client. When I started the job I have now, we had about 10+ Access DB all over the
company, now we
have a SQL2000 server with all of this fragmented db converted into one. I
can control security
all the way to the table level and limit their usage on it. I know that for non-profit org, this is not a feasible alternative but then
again how much value you can put on your data. Usually I tell my non-profit clients that their best alternative if $$ is a
concern is to invest on a Linux with MySQL enviroment with PHP, that way their ASP guys do not get too many
headaches recoding some of the queries.
Danny Thanks Marcos But Has Concerns: All our pages have been written using VBscipt ASP and Access type SQL Would a switch to Linux/MySQL mean a total re-write of the code??!!
Rob Offers This Simple Solution: just get the mySql database, the myODBC connector, and hook into it with
ADO in your ASP pages. I've done it before, no problemo.
Michael Clarifies the MySQL Solution: MySQL is available for Win32 platforms (95/98/me/nt/2k etc.)
It is free as it is now GNU'd, and open source. The ODBC connector allows you to connect to the database via ODBC, alternatively there are other methods you could use that would be faster (than odbc), if you are writing your own com object in vb, or c++.
Stephen Explains The Difference Between Concurrent Users and Connections: Danny, Keep in mind that a concurrent connection is different from a concurrent
user. If your code is written well (i.e., database connections are opened
only when they're needed and closed as soon as they're not needed any more),
just a few concurrent connections could support hundreds of users. A
concurrent connection means that an active database connection is open.
Again, in a well-written app, this is only when a SQL statement is being
executed (plus some time to set up and take down the connection). If you
have one hundred users who hit a page that uses an Access database, odds are
they all won't make a database request at precisely the same moment. While I agree that Access really should be avoided, there are too many
people who have put a rule in their mind that *anyone* who even considers
using MS Access with a Web site is daft (without really understanding the
gospel they preach). I have seen some pretty robust applications that use
Access as the back-end that perform well under some pretty decent loads
(i.e., 25,000 accesses a day). So before you panic, look at your current app. Is is working? Is the
performance acceptable? Has anyone complained? Don't fix what ain't
broken. If it's a little slow for your tastes (but no one less seems to
mind), look at improving the underlying code. You can wring some pretty big
gains out of writing better code without changing the database. Then next
time the application needs a major overhaul, consider changing the database,
but don't start mucking around simply because a few people made some on the
spot determinations about an application they've never seen or used. Any
performance enhancements you've made to get Access to work quickly, will
make the new database scream.
Michael Adds: In Access, 1) use an ole-db connection with the jet engine, not an odbc connection
2) look into getstring, and getrows whenever possible.
3) open and close the connection any time you need to run a query.
4) combine queries, and use multiple recordsets whenever possible.
5) try to only run 1 recordset query on a page. (can still return multiple recordsets.)
6) use disconnected recordsets. Example.. setup multi-recordset query (multiple selects)
open connection
open recordset object
for each recordset returned, check for .eof, and assign to a different array, with .getrows
close recordset object
close connection (if you can't run all your queries for a page with one multiple recordset returned, open the connection as late as possible, and close it as soon as possible, still use disconnected recordsets, and update via conn.exec, not via recordset row objects) NOTE: even mysql, would perform better, although it doesn't do table relationship constraints, which means you have to make sure of your constraints in code.
Roger Adds Caching to the Mix: If caching is used carefully, Access can do quite well. The library that powers RSFAST @
http://www.learnasp.com/learn/rsfast.asp
automatically caches every Access query that comes in and only dirties the
cache if the filedate/time changes. (older versions demand the programmer
specify a cache duration, now with Access it auto-caches with no additional
code) You could hit it 10,000 times for say 150 different queries, i.e. "select
.. where state='NY' ", "select .. where state='PA' ", etc. and it would
only query the data 150 times, the other 9,850 times would come out of the
caches. My informal benchmarks of this are it always is 2.5 - 6 x faster for the
cached data (and it doesn't HIT THE DATABASE thus concurrent use drops big
time except for a quick check of the file date/time) if Access is involved. SQLserver even if not cached does quite a bit of caching and 1.5-2.5 x
faster is a typical gain when this library accesses SQLserver but once
again it would reduce the people touching the database and read locks!
Stephen Concludes: Your own observations are just as valid as the word of an alleged expert.
Trust your instincts. It's very easy to follow along lists like this and
try to change everything that you know already works and works in an
acceptable manner for your situation. If an "expert" says X is true, but
you know Y to be true as well, there's no reason to jump to X. Access is not the greatest database for widespread multi-user environments.
In your particular case (and many others), Access is adequate enough to get
the job done. You'll just need to make sure that you occasionally compress
the database (if the users are doing updates and deletes) and make sure you
have good backups. The most common problem with Access in a Web environment
(besides the possibility of poor performance) is that the database file gets
corrupted. The repair feature within the ODBC manager comes in handy in
these cases (and actually works most of the time). This conversation string was taken from the 15Seconds ASP Listserv on 5/11/01. If you have an ASP-related question or would like to share some of your knowledge with others, you may join the list by clicking here.
| 
