how to connect to multiple access databases at one time

Question

Can someone please tell me how I can connect two databases at once? I need to get two sets of data for a conversion we are doing.

db1 and db2 are the names. I thought I would do:

select * from db1.table1 as table1 inner join db2.table2 as table2 on table2.column = table1.table1.column

Any help would be great. Thanks

Answer 1

Based on the title of the question it sounds like these are Microsoft Access databases, but the question itself doesn't mention if these are Access or another DB. If these are Access and you have rights to modify them, then this is an option. If not, then this will be of no help whatsoever.

If you're using Microsoft Access, why not create a linked table in one of them? Then you can query both of them in one database...

Answer 2

Access supports this kind of query:

SELECT a.d, b.*
  FROM mylocalTable AS a, 
       myLocalTable AS b IN "C:\mydir\mysubdir\myext.mdb" 
 WHERE a.id = b.id;

Alternative equivalent syntax:

SELECT a.d, b.*
  FROM mylocalTable AS a 
       INNER JOIN 
          [DATABASE=C:\mydir\mysubdir\myext.mdb;].myLocalTable AS b 
          ON a.id = b.id;

In the above example, the path to external MDB follows 8.3 file naming style.
Do a dir /x to find the name of the directory and file to see how it will look like.

e.g. c:\program files\mydatabase.mdb might look like c:\progra~1\mydata~1.mdb.

EDIT: Linked table is a better way to deal with this.