Linking Multiple Sources/Setting up Relationships

[isorders]

I have a front end with all my forms/queries/reports etc and a few tables that will contain static reference info..tblstates, tblcountry, etc.
The user specific data will be stored in two additional databases. This will permit updates to the front end with no interruption to the data in the back end.
The first BE database will contain specific company information, the second contains user specific information that can be applied to any of the companies. The user has the option to select which company.mdb file they want to work in.

Ex.
program.mdb
tblStates
tblCountries

company.mdb
tblcompany
tbladdress
tblcity
tblstate
tblprojects

standards.mdb
tblprojecttype-unique to the user, so can't set as a default in the program
tbllaborcodes-unique to the user, so can't set as a default in the program

This all worked great when the tables were in a single backend and ref. integrity kept the data accurate. The user could switch between companies with no problems, but if they entered a projecttype in one company.mdb it would have to be re-entered if they wanted to use it on another company. When I split everything up the relationships were obviously lost and I am not sure on the best way to recreate them.
Should I import/link all of the program.mdb and standards.mdb tables into the company.mdb file and re-establish the relationships there?
Thanks!!

 

[JeremyNYC]

I wouldn't have a separate database for each user. Just make one table for all that stuff and have a field that records the username for each record. Much simpler. No less secure. Allows the use of RI.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[isorders]

I don't have a database for each user, it is setup for each company. A single user needs to be able to switch between multiple company databases.
I decided to just put everything into a single database it will make things easier.
If I send an update that requires new tables to be built, I can have them run the update script on each company database.

 

[altmedia]

I have a similar problem. I work at a California State University where we have an office-specific database that we want to link with a separate, student database. Both databases were designed with Microsoft Access. Can someone explain to me how to link two microsoft access databases?

 

[JeremyNYC]

You should not have one table for each company. That's simply poor design. Read the "Fundamentals of Relational Database Design" article on my website. It will be worth your time.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[altmedia]

Actually, JeremyNYC, I don't have tables for each company or even companies at all. Are you responding to me or isorders? I have a student database that I want to link with my etext archive. Actually the student database predated my database. That's why I want to link it; it already exists, and I don't want duplication errors. I was wondering if anyone knows the mechanics of how to link microsoft databases.

 

[JeremyNYC]

Altmedia,

I am responding to the original poster. It's best to start your own thread, so that the original poster can get meaningful responses.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.