Of course, you also have to factor in whether or not the Jet/SQL database is
local or remote.  In the case of a remote database, I suspect SQL will be
faster even for a small handful of rows...perhaps even for extracting a
single byte/tinyint from a single row table.  The overhead for Jet in a
remote scenario is just too high.

But even taking them both as local copies of the data, there are just so
many factors that go into which one is "better".  Ignoring, for the moment,
considerations of knowledge base, ease of use, etc., and just looking at
speed-related issues, we have issues like:

   - Is SQL using Shared Memory?
   - How are the databases partitioned in terms of drives?  A small
database will probably do quite well in Jet on a single drive; a large
database will probably perform better being partitioned onto multiple drives
or a RAID drive.
   - As you discovered, clustered indexes are a big thing.  Jet supports
clustered indexes after a fashion, but doesn't make it obvious (as I
remember it, the Primary Key is automatically clustered, but only when you
Compact the database); SQL, on the other hand, makes it very obvious, and
you generally don't have to worry about manually initiating anything, as a
good maintenance plan will probably take care of that for you behind the
scenes.
   - Related to the above, SQL allows you to "save room" for new records,
allowing slightly-out-of-tune clustered indexes to still perform quite well
when new records are added (to a point...it probably won't work well if you
start bulk adding records).

There are probably a gazillion other factors that come into play as well;
I've just mentioned a few that came to mind off the top of my head.  Point
being, whether you use Access or SQL Server is really something that should
be decided on a project-by-project basis, and even if there's a clear
winner, you may have the issue of where your data's starting out to begin
with.  In the project I'm working on now, we had our data in Access/Jet
since before I got there in 1999.  It performed quite well, but was clearly
more suited to SQL Server in later years.  Finally, in 2005, we converted to
SQL Server with ADP front-end's, and with the exception of cross-tab
queries, I'm really happy that we did!

Later,
Rob

Dear Mark:

Your fundamentals are correct as to the reasons to choose SQL Server
products.

There is a significant sector of the market where Jet is a good tool because
the amount of time and effort and the expertise to learn it are much lower.
For limited applications written by novice to intermediate level
programmers, there's a definite niche for Jet.

For professionals who have the skill to exploit a product like Express, I
think it will probably serve poorly to use Jet in most, but not all cases.
To a large degree, this is what Brendan said as well (at least as I
interpret his words.)

It was beyond being "professionally satisfying" to have an improvement in
performance such as I experienced.  It was actually exciting.  With a bit of
fine tuning (for example, defragging the HD) I know have an application that
is 250 times as fast as what we started with.  I do not think the client
will believe me until this is installed and he sees it himself.  Would you?
Probably not, unless you had experienced similar improvements yourself in
the past.  From the point of view of an ordinary Earthling, this is from
outer space.  I just shake my head and marvel.

Tom Ellison

I see Tom's responded to this, though I haven't read it yet, but I can think
of one VERY good reason, and several lesser, more debatable ones to use Jet.

Let's say I'm leaving the office for the weekend, and want to take the
database home with me to work on over the weekend.  With Jet (assuming it's
a small project), I just copy one file, and I'm good to go.  With MSDE and
the like, there are not only multiple files to copy, but in order to do so,
you have to stop the service and/or dismount the database, etc.

Similar to the above, there's the simple fact that it's self-contained.
Your GUI, your tables, your VBA code, they're all in the same place.
Moreover, while it's generally a bad idea, if you really feel the need, you
can integrate VBA code into your queries.

And, of course, there's the learning curve.  Because of how powerful
MSDE/SQL Server are, you often need to know a great deal more than you would
were you using Access alone.  (Not to mention the freakin' cross-tab queries
I mentioned in my previous message...what a nightmare to imitate those in
SQL Server!)

Anyway, those are just some thoughts.  I agree, there's a lot to be said for
SQL Server and variants, but to suggest that it is THE solution in all cases
is a little extreme, I think.

Rob