Question

VB下使用ADO控件连接SQL两个数据库的数据合并问题

我的代码如下，运行后分别在DATAGRID1和DATAGRID2里面显示数据库ycj的L5表和数据库ecj的L5表。DATAGRID1和DATAGRID2里的表数据结构是一样的，我想在DATAGRID3里面显示ycj的L5表和ecj的L5表。既实现ycj.l5 union ecj.l5表的效果。
请给位高手给写下补充上代码吧 谢谢~~~~

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ycj;Data Source=sdz"
cn.Open
rs.Open "select * rsql1 from L5", cn, adOpenStatic, adLockReadOnly, adCmdText
Set DataGrid1.DataSource = rsql1

Set cn2 = New ADODB.Connection
Set rs2 = New ADODB.Recordset
cn2.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ecj;Data Source=sdz"
cn2.Open
rs2.Open "select * from L5", cn2, adOpenStatic, adLockReadOnly, adCmdText
Set DataGrid2.DataSource = rs2
End Sub

Answer 1

简单的办法，建立一张新表L5New
放到某个库里，然后把两个库的l5表数据写入到L5New
假设放入到ycj里。把下边的代码加入你的代码后边。

cn.exec "insert into l5new (select * from l5)"
dim iA as integer
dim rs3 as new adobe.recordset
rs3.open "select * from l5new",cn
do while not (rs2.eof or rs2.bof)
rs3.addnew
for ia=0 to rs2.fields.count-1
rs3.fields(ia)=rs2.fields(ia)
next
rs3.update
rs2.movenext
loop
Set DataGrid3.DataSource = rs3
把L5New绑定到第三个data控件

追问

你的代码加入后 编译的时候提示 未能找到存储过程 exec

追答

写错了，应该是
cn.execute "insert into l5new (select * from l5)"

Answer 2

可以先用insert into 把数据检索到一个临时表里，然后处理