ado 使用full join 查询。

hyz00001

各位好，在学习ADO的过程中遇到的问题在连接两个查询后发现会一直弹出错误。
错误为from 子句语法错误。

单独拿s1或者是s2都能运行。
一旦用上full join 语句就弹出失败。
这种情况应该会有不少人遇到，还请大神帮忙解答下。谢谢！


    Set conn = CreateObject("ADODB.Connection")   '
    Set rst = CreateObject("ADODB.Recordset")  '
    conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
    s1 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期初清单$]"
    s2 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期末清单$]"
    s = "select * from （" & s1 & ") t1 left join (" & s2 & ") t2 on t1.资产编码=t2.资产编码"
    Set rst = conn.Execute(s)
    Sheets("表").[a2].CopyFromRecordset rst

我是来讨说法的

s = "select t1.* from （" & s1 & ") t1 left join (" & s2 & ") t2 on t1.资产编码=t2.资产编码"

hyz00001

我是来讨说法的 发表于 2019-1-23 13:15
s = "select t1.* from （" & s1 & ") t1 left join (" & s2 & ") t2 on t1.资产编码=t2.资产编码"

感谢你，只不过还是弹出那个提示，就是在运行到查询的时候弹出来的。

hyz00001

后面我自己想了好久，直接使用两个表查询，不经过sql子查询来弄。尝试了，也不行。
那个重要的代码被变更为如下：
s = "select t1.资产编码,t2.资产编码,t1.原值本币 as bg原值,t2.累计折旧 as ed累计折旧 from [期初清单$] t1 join [期末清单$] t2 on t1.资产编码=t2.资产编码"

谁能告诉我在excel表里面怎么进入那个有点像sql界面的按钮。或者是帮忙想想办法怎么样把这个搞定。

弹出的错误如下：


全部的代码如下：
    Set conn = CreateObject("ADODB.Connection")   '
    Set rst = CreateObject("ADODB.Recordset")  '
    conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
    s1 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期初清单$]"
    s2 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期末清单$]"
    s = "select t1.* from （" & s1 & ") t1 left join (" & s2 & ") t2 on t1.资产编码=t2.资产编码"
    s = "select t1.资产编码,t2.资产编码,t1.原值本币 as bg原值,t2.累计折旧 as ed累计折旧 from [期初清单$] t1 join [期末清单$] t2 on t1.资产编码=t2.资产编码"
    Set rst = conn.Execute(s)
    Sheets("表").[a2].CopyFromRecordset rst

魂断蓝桥

access 不支持full join

hyz00001

魂断蓝桥 发表于 2019-1-23 14:02
access 不支持full join

谢谢你，可是我现在已经改成了join ,还是会弹出这个错误。请问有没有办法合并两张表条件是资产编码相同。

xyxcc177

s = "select t1.*,t2.*  from （" & s1 & ") t1 left join (" & s2 & ") t2 on t1.资产编码=t2.资产编码"

amazeyeli

select * from t1 full join t2 on
=
select * from t1 left join t2 on
union all
select * from t1 right join t2 on

amazeyeli

inner join 在 excel 中 inner 不可省略

hyz00001

谢谢您。

上午的那个已经搞定。主要是括号使用了中文状态的括号。
但另外一个问题是：两张表的连接没问题，三张表的连接就弹出了错误。
下面代码第一行的ok。
第二行弹出错误。

1. s = "select * from (" & bm & ") t1 left join (" & s1 & ") t2 on t1.资产编码=t2.资产编码"
   
2. s = "select * from (" & bm & ") t1 left join (" & s1 & ") t2 on t1.资产编码=t2.资产编码 left join (" & s2 & ") t3 on t1.资产编码=t3.资产编码"
   

复制代码

还请帮帮我，谢谢！