Selecting 2 tables from 2 different databases (ACCESS)

by

You can use IN:

SELECT t1.*, t2.* 
FROM T1 
INNER JOIN 
(SELECT * FROM atable 
IN 'C:\Docs\DB2.mdb') t2
ON t1.ID=t2.ID

EDIT:

sc = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\docs\other.mdb"
cn.open sc

s="SELECT * FROM t1 INNER JOIN " _
& "[MS Access;PWD=databasePWD;DATABASE=C:\docs\db.mdb].t2 ON t1.ID=t2.ID"

rs.Open s, cn

EDIT 2:

You can use the aliases to identify which database a field is from:

s="SELECT * FROM table1 t INNER JOIN " _
& "[MS Access;PWD=databasePWD;DATABASE=C:\docs\db.mdb].m ON t.ID=m.ID"

 msgbox rs.fields("m.code") & " " & rs.fields("t.code")

EDIT 3

Or you can add a virtual field:

 SELECT 1 AS "DB", Field, Field FROM  ...
 UNION ALL
 SELECT 2 AS "DB", Field, Field FROM

UNION ALL is usually faster.

More Related Contents:

  1. What are the rules governing usage of parenthesis in VBA function calls?
  2. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. SQL Query – Using Order By in UNION
  6. create form to add records in multiple tables
  7. Operation must use an updatable query. (Error 3073) Microsoft Access
  8. Access-SQL: Inner Join with multiple tables
  9. How do I force VBA/Access to require variables to be defined?
  10. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  11. Running Microsoft Access as a Scheduled Task
  12. How to restart counting from 1 after erasing table in MS Access?
  13. Equivalent cURL in VBA?
  14. MS Access trigger?
  15. Insert SQL command with Datetime in MS-Access
  16. How do I execute multiple SQL Statements in Access’ Query Editor?
  17. VBA procedure to import csv file into access
  18. How to select top 10 in Access query?
  19. Transform multiple rows into columns with unique key
  20. how to show query results in a datasheet in the same form?
  21. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  22. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  23. Null values for variables in VBA
  24. Custom row source for combo box in continuous form in Access
  25. MS Access query returning Chinese characters – possible table corruption?
  26. Read and write to an access database using Javascript
  27. Using a centralized VBA module in multiple Access databases
  28. How to execute a query in ms-access in VBA code?
  29. How to add a menu item to the default right click context menu
  30. Force all users to disconnect from 2010 Access backend database

Leave a Comment