"ARC" <an**@andyc.comwrote
. . . Unfortunately, when you run a query on
jet, it sends the entire table (or all entire tables
contained in a query) accross the wire to the
workstation, then runs the query there.
That is not necessarily true; Jet treats the MDB file in the shared folder
the same as it would a file on the local hard disk. It retrieves only what
it needs to accomplish the task. If you have properly indexed your tables,
and are using a WHERE clause that refers to the indexed field, it will
retrieve the index, locate the desired record(s), and only bring back
that(those) record(s).
Only if you haven't indexed, or aren't using the index as your criteria, or
your criteria is a calculated field will it bring back as much data as you
imply that it always will. That's why, as one of my wise consultant friends
is fond of saying, "You've gotta know what you're doing."
There is at least one book dealing with how the Jet DB engine worked (in the
particular version about which the book is written) that is still largely
accurate. That is, some details have changed, but not the basics of how Jet
works. It was published in 1996, by Microsoft Press, and is by Haught,
Ferguson, et al. If you are interested, you'd probably have to find a used
copy... I seriously doubt it is still in print, eleven years later.
In using Access since 1993, I think every Access client to server DB
application with which I've worked was been made client-server for
reliability, logging, and recoverability -- not because of performance, nor
number of users. Some are, I'd guess, but more are converted because of fear
of performance problems than because of encountering performance problems.
Corporate IT departments are often the "fearmongers."
Larry Linson
Microsoft Access MVP