sql语句问题,我觉得没有错误,但是始终就是出错,还请帮忙看看.
dim cn as new adodb connection
cn.proder="driver....jet4.0"这里没有错误.这样写没有问题,就是同一个数据库里面的表:
cn.execute "update (select * from tbl1 inner join tbl2 on tbl1.代码=tbl2.代码) set tbl1.数量=tbl2.库存量"但是如果两个表不在同一个数据库,就不行了,不知道应该怎么解决:
cn.execute "update (select * from tbl1 inner join tbl2 in 'd:\sjk2.mdb' on tbl1.代码=tbl2.代码) set tbl1.数量=tbl2.库存量"
改成下面这样也不行:
cn.execute "update (select * from tbl1 as t1 inner join tbl2 in 'd:\sjk2.mdb' as t2 on t1.代码=t2.代码) set t1.数量=t2.库存量"我实在不知道应该怎么写了还请高手帮忙.谢谢.
要求就是把不同的两个数据库里的表,按照代码相同的条件,进行修改.请大家帮忙 .
dim cn as new adodb connection
cn.proder="driver....jet4.0"这里没有错误.这样写没有问题,就是同一个数据库里面的表:
cn.execute "update (select * from tbl1 inner join tbl2 on tbl1.代码=tbl2.代码) set tbl1.数量=tbl2.库存量"但是如果两个表不在同一个数据库,就不行了,不知道应该怎么解决:
cn.execute "update (select * from tbl1 inner join tbl2 in 'd:\sjk2.mdb' on tbl1.代码=tbl2.代码) set tbl1.数量=tbl2.库存量"
改成下面这样也不行:
cn.execute "update (select * from tbl1 as t1 inner join tbl2 in 'd:\sjk2.mdb' as t2 on t1.代码=t2.代码) set t1.数量=t2.库存量"我实在不知道应该怎么写了还请高手帮忙.谢谢.
要求就是把不同的两个数据库里的表,按照代码相同的条件,进行修改.请大家帮忙 .
dim cn1 as new adodb connection
cn1.proder="driver....jet4.0"'连接数据库1dim cn2 as new adodb connection
cn2.proder="driver....jet4.0"'连接数据库2cn1.Execute("update ....")
cn2.Execute("update ....")
cn1.proder="driver....jet4.0"'连接数据库1
cn1.CursorLocation=adUseClient
cn1.Open
dim cn2 as new adodb connection,rs2 as new adodb.recordset
cn2.proder="driver....jet4.0"'连接数据库2
cn2.CursorLocation=adUseClient
cn2.Openrs1.Open "select top 1 * from 表",cn1,3,2
rs2.Open "select * from 表 where ....",cn2,3,2 '从库2中取出记录,准备用来更新库1的表
dim strTemp as String,i%
For i=0 to rs1.Fields.Count-1
strTemp=strTemp & rs1.fields(i).Name & "='" & rs2.fields(i).value & "','"
Next
strTemp=left(strTemp,len(strTemp)-2)'去掉后面多余的逗号和引号
cn1.Execute("update 表 set " & strTemp)
cn.execute "update (select * from tbl1 inner join [d:\sjk2.mdb].tbl2 as t on tbl1.代码=t.代码) set tbl1.数量=t.库存量"d:\sjk2.mdb有密码的话:
cn.execute "update (select * from tbl1 inner join [d:\sjk2.mdb;pwd=123].tbl2 as t on tbl1.代码=t.代码) set tbl1.数量=t.库存量"
select *
from tbl1 as s inner join [d:\sjk2.mdb].tbl2 as t
on s.代码=t.代码update (select *
from tbl1 as s inner join [d:\sjk2.mdb].tbl2 as t
on s.代码=t.代码) as q
set s.数量=t.库存量