Dear Kris:
The Jet database is a "shared file" based database engine that allows each
client computer (front end) to access the same files, so that all the
logical work has to be done on the front end.
There is a different model for database engines called "client / server" in
which the client typically sends the SQL for an operation and all the
processing is done on the server. Any necessary results are what is sent
back to the client.
There are many advantages to client / server processing, but a few
drawbacks. Some of the power of Microsoft Access "Jet" has exploited
feature that are not appropriate in a client / server environment. For
example, a query can access the value or other property of a control on the
screen, or can use a function you have written in the front end. With the
client / server model this changes. For example, in order to reference the
value in a control, you must extract that value and pass it in some for to
the server. Instead of writing functions in the front end, you would write
those functions for the server. All these differences can be overcome, but
it will mean learning a different technology.
Client / Server has been the "coming technology" for some time. Shared file
database engines are going to see limited, if any, new development, while
client / server development continues to be extensive. The major products
for client / server are Microsoft's SQL Server and Oracle. Compared to
Access, they will seem pretty expensive, but keep reading, there is some
good news in this regard.
Your post points out one of the advantages. If the database requires
considerable extensive processing, you can purchase a single powerful
computer for a server and concentrate the processing there. In the "shared
file" model, every client that needs to perform extensive processing would
need to be upgraded if things need speeded up.
In addition, due to various factors, client / server is often much faster
anyway. This may be in large part because it is the client / server engines
that have been receiving the funding for further development, which includes
optimization of the processing. I have processes that exploit features of a
client / server database engine that don't exist in Jet. These processes
were originally written for Jet. One important process was taking 20-30
minutes using Jet. Now, there is much more data than there was 2 years ago
when we were doing that with Jet, so the same processing would now take up
to an hour. But using client / server and exploiting some of its
capabilities, we are accomplishing the same thing in 3 - 4 seconds, and
increase in performance of more than 500 fold.
Another advantage is reduced bandwidth. A client / server application can
be designed to work over a low bandwidth connection, and will load an
existing LAN less than a "shared file" engine in many cases.
Starting with Access 2000, Microsoft has included both the "shared file" Jet
engine of its earlier releases, and also a new engine called MSDE. This is
a somewhat limited version of SQL Server. But be warned. There is a pretty
steep learning curve to move applications from one to the other, especially
if you want to see all the advantages outlined above. In addition, Access
includes an alternative front end design system called ADP that is designed
much like MDBs to which we are accustomed, but integrates directly and
easily with MSDE or "full" SQL Server editions. Also, anything written for
MSDE migrates seamlessly to SQL Server, and vice-versa. For example, I
design for SQL Server and install on clients with MSDE.
