从2个不同的数据库中选择2个表(访问) [英] Selecting 2 tables from 2 different databases (ACCESS)

问题描述

这是我的联系

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

所以要使用2个数据库，我需要2个adoCon，当我执行select时，我需要从每个数据库中选择一个

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具有类别，而db2.tblcats具有类别和子类别 因此，除了能够同时选择两者之外，我还需要能够从数据库中得知猫是什么

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

获得大帮助后的第2步

这是我的代码

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

推荐答案

您可以使用IN:

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

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

您可以使用别名来标识字段来自哪个数据库:

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")

编辑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通常更快.

UNION ALL is usually faster.

这篇关于从2个不同的数据库中选择2个表(访问)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！