Selecting 2 tables from 2 different databases (ACCESS)

Question

hi guys,

here is the connection i have

strCon="DBQ=" & Server.Mappath("db.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};PWD=password;"
set adoCon=server.createobject("adodb.connection")
adoCon.Open strCon

so in order to work with the 2 databases i have 2 adoCon and when i do the select i select from each db i need

now for the problem... in this situation i will be able only to get all the info from one and then from the other one. but what i want is to be able to put the together.

db1.tblcats has categories and db2.tblcats has categories and subcategories so in addition to be able to select both of the together, i need to be able to know what cat is from what db

---

Step 2 after the big help

this is my code

strSQL = "SELECT name FROM tblcats union " _ 
& "select name from [MS Access;PWD=pass;DATABASE=" & Server.Mappath("../shop.mdb") & "].tblcats as bcats where bcats.father=50"
                                rs.CursorType = 3
                                rs.LockType = 3
                                rs.Open strSQL, strCon
while not rs.eof
response.write rs("name")&"<br>"
rs.movenext
wend

how can i know what record came from what db? cause i need to act difrently for each one

Answer 1

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.