Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    25

    Unanswered: Join two tables from separate databases?

    Hi All,

    Is it possible to join two tables where each table is in its own access 2000 database, and they both exist in the same directory?

    Thanks.

    Matt

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by mlong30
    Hi All,

    Is it possible to join two tables where each table is in its own access 2000 database, and they both exist in the same directory?

    Thanks.

    Matt
    Sure. From the database you want to run the query in, either:

    1. Choose File / Import / Link Table and link the table from the other database in. It will behave like a local table.

    2. Use SQL view and alter the FROM clause to say FROM tableFoo IN .\bar.mdb.

    3. Use VBA to link the table from a visual basic module. This one is more tricky, but it can be all dynamic, e.g. prompting the user intelligently if it doesn't find the database file.

    Now, after you've done all this, you need to test for what Access is going to do if you move them. In a reasonably good world, a relative path reference would mean you could move the folder containing both MDB files and Access wouldn't care. In a Microsoft world, you might not get a relative path reference because they didn't think anyone would ever want to move a file.

    I don't think option 1 allows a relative reference, but even so, it can be reconnected with the linked tables manager somewhere in the Tools menu. Option 2 should work, but I haven't tested it. Option 3 is as robust as you make it.

Posting Permissions

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