Relationships between Multiple Databases

[TimTang]

I'm not very experienced with Access or databases in general so this may be a simple question for the experts out there.

Is there some way of creating relationships accross multiple database files.

I'm trying to make a furniture catalogue. There are about 5 or 6 different catagories and I would like to have them in separate files rather than one big file.

I've also got Contacts, Staff, and projects in separate files which I would like to tap into to create reports and query's.

So far I've only had to work with one file where all the tables and relationships are self contained.

If possible maybe someone could recommend a good tutorial to accomplish what I'm trying to do.

Thanks in advance for any assistance.

 

[lynchg]

You can look at the Northwind database, it is the sample database that comes with Access. When you open Access, choose the Northwind database from the list of existing databases. then select Tools/Relationships from the menu bar. It should give you some direction on how relational tables work. And look at the data in those tables, you'll see how products and categories relate to each other, etc.

 

[PHV]

You may also have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TimTang]

Thanks to both of you.

lynchg: I already have a good understanding of relationships within any given database, but I want to create a relationship accross two separate databases. Maybe this isn't possible?

PHV: I've already read the article you recommend, and it is very good, by the way.

 

[TimTang]

I just found the answer to my question. Strangely enough it was under:

How do I setup Ms Access 97 to access a MySQL server via ODBC?
faq436-255
Posted: 13 Oct 00

1. Open MS Access and create a new database.
2. From the file menu, choose File and then Get External Data and then Link Tables.
3.From "Link" window that comes up, choose ODBC Databases.
4. From the "Select Data Source" Window, choose the DSN name you've setup for your online mySQL database.
5. From the "Link Tables" window, select the table you want to attach and click the OK button.
6. ODBC could prompt you for the primary key column in that table, if it can't find it. Specify one and that's it! The online mySQL table is now attached to your local access database:
7. You may open the table, update, insert or delete records. To attach all database tables, follow the above steps for each table in your database.

I did the above proceedure using another Access mdb file and now the linked table shows up in the relationships window. As promised in step '7.' I can add, delete, and edit any data in this table as if it were part of the same database.

This is really good news for me as I want to keep data organized in separate smaller databases rather than one humongus database.

Cheers!