Relating fields between multiple DBs...

[ben.mooreNO]

I am relating a database of jobs performed for any certain company to a
database of contacts we may have for that company by company name. The
jobs database and the contact list database both have fields called
company name (or similar), here's the question:

Is it possible to related the fields across two databases? Or is it more
efficient to link the table in, then relate the fields? Just wondering
what the best approach is from a design point of view. Any help is
greatly appreciated. Thank you in advance.

cheers,
Ben Moore

 

[Douglas J. Steele]

It's not possible to relate fields across two databases.. And you can't link
the table and use it in a relationship.

You can only set up relationships (and hence Referential Integrity) for
tables within a single database. For any other situation, you'll have to
write your own code to enforce RI.

Note that you'll need to add RI code to both databases, and even that's not
going to guarantee anything (since people could connect to one or the other
database using another front-end that doesn't include RI)

 

[ben.mooreNO]

Any examples on who to include in said relational code? All I need is a
form that will show the customer's contact info in one page of a tab
control, and show the datasheet view of the job records from the other
database. I understand I should figure this out on my own, but cannot
find resources from microsoft on a complex relationship such as this.
Should I use some VBA and SQL statements for the form? Im really lost,
but think this relationship between these DBs has potential to draw
different departments together a little more.

Thanks for your insight Doug... Any further advice from anyone is
greatly appreciated.

cheers,
Ben Moore

 

[Douglas J. Steele]

I may have misunderstood what you meant by "relating fields".

I thought you wanted to be able to enforce referential integrity between the
tables, so that you couldn't enter a value in Table 1 in Database A unless a
corresponding value existed in Table 1 in Database B. Now I'm not as
certain.

If all you're trying to do is retrieve information from Table 1 in Database
A corresponding to the value in Table 1 in Database B, then you're probably
best off creating a linked table, and just creating a query that joins the
two tables.

 

[ben.mooreNO]

Alrighty, thank you. I don't need to enforce referential integrity
AFAIK, I've been trying different query combinations all day, but all my
subforms just lock up. Oh well, I will keep trying. Thanks a bunch.

cheers,
Ben Moore