Need Help with multiple databases

[Brad_A]

I apparently lost the person helping me so I will start over here in hopes of
getting some help from someone that is more familiar. Once I do it once, I
learn it, but this is my first time for setting up a database with access.

I have 7 facilities... I want each facility to have a simple form to enter
data with. I want each facility to only have access to their own facility's
database. I want each of these facilities information placed into a master
database to run reports, queries, etc. for analysis.

Thus, I want the table Main_DB (the main database) to contain the informaton
for tables Facility1 through Facility7. How do I do this? All fields are
the same in every database. Please use the names of Facility1-7 when writing
code so I can better understand it.

 

[John Vinson]

I apparently lost the person helping me so I will start over here in hopes of
getting some help from someone that is more familiar. Once I do it once, I
learn it, but this is my first time for setting up a database with access.

I have 7 facilities... I want each facility to have a simple form to enter
data with. I want each facility to only have access to their own facility's
database. I want each of these facilities information placed into a master
database to run reports, queries, etc. for analysis.

Thus, I want the table Main_DB (the main database) to contain the informaton
for tables Facility1 through Facility7. How do I do this? All fields are
the same in every database. Please use the names of Facility1-7 when writing
code so I can better understand it.

The critical question is - are the seven facilities geographically
separated, or all in the same place? Access is not well adapted to use
over a wide-area network (except by using Terminal Server or Citrix,
or implementing a web application). If the data entry for the seven
facilities is being done on a single fast, stable LAN, a single
database would be preferable; if you have to EMail or FTP the data
from one location to another, each facility should have its own
database, and you'll need to use Access Partial Replication to merge
the data. This is not for the faint-hearted!

As for the data structure, if you have seven identically-structured
tables, you should VERY strongly consider instead uing one single
table. This table would have a Facility field with values Facility1,
Facility5 etc. The users would not have access to this table directly;
instead, you would have Queries selecting just the data for each
facility, and the seven users would have their Access Security
permissions set so that they can see and update their own query (but
none of the others).


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps