Interface for Multiple Access Data Bases ???

[Will]

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.

Thanks for any help.

 

[Larry Linson]

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

 

[Will]

Larry,

Currently only two users on our peer-to-peer network. Ultimately maybe 3 or
4. But most likely never more than 2 or 3 at a time... if that.

Setting asside the number of users for the moment.

- How can I 'split' the front end stuff from the back end stuff as you
outlined?
- I had thought of taking each Access data base we use and putting all the
tables into a single Access mdb file... then have the querries and forms
available for each in the same mdb file... then putting that mdb file on the
shared folder on the network drive.

Is that what you are talking about or is there a way to create a 'front end'
mdb file that links to a seperate 'back end' mdb file?

thanks again for your help.

Will

 

[Larry Linson]

Some peer-to-peer networks (don't ask me the details or a link) limit you to
5 concurrent connections. That, I think, is not enough to support even a
couple of users _adequately_. One at a time should work nicely.

I have generally split my databases manually: I make two copies, the I go in
and delete everything I don't need in the front end from one and everything
I don't need in the back end from the other. I place the back end on the
server, then use Link Table Manager to set up the joins. Or, there is a
database splitter

Somewhere in the Tools menu hierarchy, I think, is a utility called Database
Slitter that could be used, too.

Larry Linson
Microsoft Access MVP.

 

[Will]

Larry,

thanks for the info.

I dug around In Access after your initial prompting and found info in the
Access Help.

thanks again.