468,771 Members | 1,929 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,771 developers. It's quick & easy.

Relationships and where to build them?

Ron
Hi All,

I have relationships on a database built in the back end, but I'm wondering
if that's where they should be. Can they be built in the front end but
apply to all the back end tables? Can someone advise?

Here's the scenario. User has a choice upon first entry of the program to
go into one of 10 separate databases, all with the same tables but with
different data stored in each... let's say "office". They go into "Office
1" and they use/alter/report on, etc the data for that office. When done
with that, go to the menu of offices, select another and do work in that
office, etc. 22 tables in each "office".

Within each database I have a table for zip codes, cities and states. The
user selects the zip code and it pops in the city, state on the form (only
storing zipID in the table). The user, upon finding a new zip code,
however, would need to enter it and save it into the zipcode table in each
of the different databases if they want to access it within the different
databases. Cumbersome to say the least. And certainly uncool.

It'd probably be better, to have the zipcode table in another database and
just link it into the tables when linking the others. But then, how do I
enforce referential integrity? I can't do the relationships to the main
back end tables since the zipcode table will be in another database. Right?
Do I link it into my back end database, have the relationships there, and
then link into my front end?

Is my question clear? ::smile:: (heck, I'm confused by it!!!)

Because of ownership of the "unique" tables within each database, I need to
keep the databases separate like this, but certainly that wouldn't apply to
the zipcode table, nor about 3 other tables that COULD be set up like this
if I can figure out how to do it.

TIA
ron
Apr 4 '07 #1
1 1395
Relationships are on tables, so they must be on the back-end The front-end
can only put relationships on local tables.

It may look like those linked tables have a relationship on the front-end,
but you'll notice that you cannot enforce referential integrity, so all you
are really doing is defining joins for queries.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Ron" <ro********************@verizon.netwrote in message
news:bnRQh.8413$P84.4209@trnddc07...
Hi All,

I have relationships on a database built in the back end, but I'm
wondering if that's where they should be. Can they be built in the front
end but apply to all the back end tables? Can someone advise?

Here's the scenario. User has a choice upon first entry of the program to
go into one of 10 separate databases, all with the same tables but with
different data stored in each... let's say "office". They go into "Office
1" and they use/alter/report on, etc the data for that office. When done
with that, go to the menu of offices, select another and do work in that
office, etc. 22 tables in each "office".

Within each database I have a table for zip codes, cities and states. The
user selects the zip code and it pops in the city, state on the form (only
storing zipID in the table). The user, upon finding a new zip code,
however, would need to enter it and save it into the zipcode table in each
of the different databases if they want to access it within the different
databases. Cumbersome to say the least. And certainly uncool.

It'd probably be better, to have the zipcode table in another database and
just link it into the tables when linking the others. But then, how do I
enforce referential integrity? I can't do the relationships to the main
back end tables since the zipcode table will be in another database.
Right? Do I link it into my back end database, have the relationships
there, and then link into my front end?

Is my question clear? ::smile:: (heck, I'm confused by it!!!)

Because of ownership of the "unique" tables within each database, I need
to keep the databases separate like this, but certainly that wouldn't
apply to the zipcode table, nor about 3 other tables that COULD be set up
like this if I can figure out how to do it.

TIA
ron

Apr 4 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Jeff | last post: by
2 posts views Thread by Max | last post: by
7 posts views Thread by davegb | last post: by
13 posts views Thread by ARC | last post: by
5 posts views Thread by LittleCake | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.