Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Tables (2002)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Tables (2002)

    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...

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2002)

    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

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linking Tables (2002)

    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>
    Wendell

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2002)

    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linking Tables (2002)

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Tables (2002)

    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.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •