I disagree with James on several issues... although, if you have used some
common, but inefficient, approaches (e.g., displaying some fields of every
record in a table/query, then double clicking one to get the deatils) what
he says can happen; if you have used careful, efficient approaches to
mulituser, you are likely to get better response. If you then tailor your
approach to the client-server environment, it will be even better.
The Jet (and is current descendant, ACE) database engine is surprisingly
efficient. Despite those who have claimed that you "bring over all the
records" from a split or a server database, that has never been the case.
Depending on the requirements and implementation, split Access MDB databases
have happily supported well over 100 concurrent users. If poorly designed
and implemented, I am certain you can overwhelm your machine/LAN with just a
handful of users.
(I have not seen compable statistics on ACCDB or ACCDE databases. But since
ACE is a "direct descendant" of Jet, with enhancements, I would guess it is
in the same range.)
The number of concurrent users has little to do with database corruption
(unless you have concurrent users sharing the same copy of the front-end,
user-interface database). As far as I have been able to determine, the only
limit to the number of users supported in a LAN-based ODBC-linked-table
client-server environment is how many the server DB can support. I
personally worked on an Access-Informix ODBC-linked database back in Access
2.0 days that had approximately 275 users the last time I was associated
with it.
However, I agree with his recommendations -- careful understanding of the
way that both Access and the server DB work, and careful design to take
advantage of that, can make a significant difference.
Larry Linson
Microsoft Office Access MVP
Speed and file size issues are driving me to consider a server (MYSQL or
SQL Server) back-end. I love Access of course, and want to keep the front
end work anyway.
Can I be lazy and just link to my tables in the back ? Or must I dispense
with bound forms and work at coding my data entry forms ?
I understand (tell me if I'm wrong ) that the speed issue with Access on a
LAN is based on the entire MDB (it's already split) holding the data being
brought over the network to the client computer. Will this happen anyway
if
the forms stay bound to a server backend ?
Barrett,
Going to a server backend is unlikely to help much with speed until
you are able to take advantage of the particular strengths of a server
backend. A simple one to one data conversion is likely to result in
speeds similar to what you are now experiencing. Keeping an Access
frontend with a server backend is often advantageous because Access'
RAD capabilities make the RAD capabilities of, say, Visual Studio look
like they are not RAD at all. You can be lazy and just link to your
tables in the back. You can still use bound forms as you've always
done, unless you are planning to have, say, 100 simultaneous, editing
users. A carefully crafted Access database with an Access backend on
the LAN can manage about 42 simultaneous, editing users. The improved
capability of server backends in preventing corruption makes me guess
that they can handle more than double the number of simultaneous,
editing users before there is a significant risk of either corruption
or performance robbing conflicts. Note: I have not actually tested to
see if 100 is correct. Such a test might take several weeks to
perform. Even with Access alone, file size constraints can be
overcome by linking to multiple backend databases unless you have a
just single, extremely large table
. Finally, make sure that you
have optimized your code in Access as much as possible. It is often
possible to come up with better and faster plans for handling data
than the first idea you come up with.
James A. Fortune
(e-mail address removed)
