Linking Tables (2002)

[DCOLES]

I have access loaded on our network and thought I might put the static lookup tables in one database and the transaction tables in another.
This doesn't appear to work. Is this possible? Is there a special way to link? Is it probably a network thing? or am I out of luck.

any information would be appreciated.

[HansV]

Although it's more common to have a frontend/backend split where all tables are in one database (the backend) and everything else - queries, forms, reports, macros and modules - in another (the frontend), your setup should work too.
If you have put your lookup tables in database A and the rest in database B, you should put links to the lookup tables in database B. To create linked tables, use File | Get External Data | Link Tables...

[DCOLES]

Hans
I tried that - I got an error message. I'll try it again and if I still get the error message I'll ask again with more information.

thanks

[WendellB]

What did you mean when you said lookup tables? Are you using combo boxes to display text rather than numbers for your transaction tables? If that's the case, I'm not sure you can use the lookup feature - if you can, you probably need to link them to the back-end that contains the transaction tables. In any event, lookup tables do add a performance penalty that you may not want in the long run. In addition, the generally accepted design practice says don't let people play with the data at the table level - make them use forms, and the forms can use a combo box to display the text implied by the numeric value. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

[DCOLES]

Wendell
What I have is about 10 tables that are used with multiple databases and contain data that hardly ever changes. Then I have other tables that change all the time with data entry by users. I was thinking that putting the common tables in one database will work better because I am consistently needing to access a transaction table in an unrelated database which based on our network setup I cannot do. It's very cumbersome having multiple database with multiple back ends that contain the same data tables.

I get an error message when I try to link tables to 2 different databases. Like I told Hans, I am going to try it again to see if it was just a bad day.. and if I still get the message then I can write it down and ask for more specific help.

hopefully that makes thinks a bit clearer as to my problem.

thanks

[patt]

What is the error message that you get?
When you link the tables to database A do you nominate only those tables that are in database A or do you nominate all of the tables?

[charlotte]

If you are using an MDB in Access 2002, there is no reason why you shouldn't be able to link to more than one database. However, you won't be able to do it with a wizard. YOu will need to create the links by using File-->Get External Data and selecting the database for one set of links, creating those links and then doing it again for the other database. If you are getting an error message, you need to tell us exactly what that message is. You also didn't tell us whether you were creating the links from the User Interface or thorugh code.

[dazednconfused]

This may not be a valid offering, but it caused me some headaches untill I spotted a post on here, when you link the tables go through the 'my network places' option, rather than the more obvious 'My Computer'. The network my database is on has a few drives and they are not all mapped to the users PCs with the same drive letter, but thay all have the same network names etc. This probably isn't your problem, but if you are going to use the File, Get external data option it may save you the headache I had.

Ian