从 2 个不同的数据库中选择 2 个表 (ACCESS) [英] 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，当我进行选择时，我从我需要的每个数据库中进行选择

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:DocsDB2.mdb') t2
ON t1.ID=t2.ID

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

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

rs.Open s, cn

编辑 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:docsdb.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 个表 (ACCESS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！