The company I work for has several multi-user systems running on VB4 and 5
using Jet as the database engine. We have had very mixed results. Some sites
are fine with potentially up to 10 users hitting the database at once, while
others have problems with even 2 concurrent users.As a result, we are thinking
of upsizing to SQL Server, but it will raise the price of the product, and is
to be avoided if  possible,so my boss tells me :-)  Is there any definitive
information on just how many users a Jet database engine, and whether the
method of access used affect this?

Ian Harkin

I've seen a figure for the maximum number of connections an Access
database can support. However, I don't recall _what_ the number is, so I
won't mislead you with a wild guess. It was well in excess of ten
however.

In my experience, the application (and therefore the developer) can make
a _world_ of difference in terms of how many simultaneous instances of
the application can be working with a database at once. Applications
which are heavy on data controls, query on entire tables, use
cursor-driven methods (e.g., .Edit-.Update sequences, .Search,
..FindNext, etc.) instead of SQL, and work against tables with no
indexes, or poorly choosen indexes, tend to have the most problems in
multi-user situations.

Applications which use SQL to get precisely the data they need, don't
hold references to database objects one clock cycle longer than
necessary, and are based on intelligently designed queries tend to have
few multi-user issues. (It also helps to _plan_ for the occasional lock
contention, and work out a strategy for retrying operations if they fail
once or twice due to locking problems.)

Finally, VB5 and DAO 3.5 (Access 97) will give you much more pleasing
results than VB4 and DAO 3.0 (Access 95).

Enjoy...