We have several Access Data Bases
that we are constantly using.
Can anyone tell me the best way to
provide a single interface to these
multiple databases?
Otherwise we end up with multiple
copies of Access running at once on
each computer.
(We are running a peer-to-peer
network with one computers disk setup
as the "network drive" where we keep
all files so that everyone can access them.
To provide a single user interface, you are going to have to (carefully)
combine the Access UI for each of these applications into a single Access
database. Be certain to split the front-end (queries, forms, reports,
macros, modules, and perhaps local lookup tables) from the back-end (tables,
relationships, and data). Give each user a copy of the front-end on the
user's own machine and link the tables in the back-end in a shared folder.
Having multiple users logged in to the same front-end or monolithic database
greatly increases your chances of database corruption.
There's an introductory presentation on Access in a Multiuser Environment
that I did for my user group that you can download from
http://appdevissues.tripod.com. It will identify topics that I thought
worthwhile to discuss, and a bit more. The best collection of detailed
information and links on the subject of Access in the multiuser environment
is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.
Factors in how many users can be supported in multiuser include the
requirements, design, and implementation of the database application and the
hardware, software, and network environments. If all factors are near
perfect, we have reliable reports of over 100 concurrent users. Even if not
all are near perfect, we routinely see reports of 30 - 70 users. But, in
cases where we are rather sure that all are about as far from perfect as can
be, people have reported Access "falling over" with as few as four users.
I'd venture to guess that if someone went out of their way to do everything
wrong, it would be possible to create a database that wouldn't even support
one or two users. <GRIN>
Alas, using a peer-to-peer shared folder is one of those things that will
_severely_ limit the number of concurrent users because of the very limited
number of connections the peer-to-peer environment allows. It would really
be a great improvment if you were to configure an actual domain server,
because those allow enough connections to support a large user audience.
Larry Linson
Microsoft Access MVP