vb6如何用ado跨数据库查询多个xls的数据

chr78228

不要说我走错地方了,确实我是想通过vb6而不是vba实现跨库查询


只因vb6现在人单势微,vba和vb6又基本通用,所以在这里求助了,具体问题如下:


想通过sql查询提取多个xls里的数据,网上查了一下可以把xls直接当成数据库直接用sql操作提取数据,但尝试了很久都不知道怎么做.

单个xls的sql操作(单表,多表)马马虎虎学得可以操作,但源数据是多个xls,在不汇总各个xls的情况下,想直接操作多个xls就一直没学会.

各位能否驻足停留片刻,指点一下:

假设有1.xls,2.xls,3.xls三个文件,每个xls里有一个表,分别为表一,表二,表三.

select* from 1.xls.表一,2.xls.表二,3.xls.表三 where 表一.编号=表二.编号=表三.编号

我只是想说明我的意图,实际操作上不知道怎么用具体的代码定义.和编写

麻烦各位了,从定义到执行 希望能给个具体的实例代码,谢谢了!

abc123281

确实走错地方了   这里很少有人研究vb6

chr78228

提示 至少有一个参数没有被指定值 我检查了N遍,头都晕了


Private Sub Command1_Click()
    Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim SQL$, sqlA$, sqlB$, sqlC$, sqlD$, sqlE$, link$, i As Long
    'link = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\My Documents\Desktop\bhTool\补货明细.xls;Extended Properties=Excel 12.0"
    link = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\Desktop\bhTool\补货明细.xls;Extended Properties='Excel 8.0';Persist Security Info=False"
    conn.Open link
    sqlA = "(select t1.补货仓库,t1.商品分类,t1.商品名称,t1.规格,t1.单位,t1.商品编号,t1.数量,t1.商品备注 from [补货明细$]) t1"
    sqlB = "(select t2.仓库,t2.编号,t2.总库存数 as 门店库存,t2.寄存数 as 门店寄存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    sqlC = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    sqlD = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    sqlE = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
    '    sqlA = "(select t1.补货仓库,t1.商品分类,t1.商品名称,t1.规格,t1.单位,t1.商品编号,t1.数量,t1.商品备注 from [补货明细$]) t1"
    '    sqlB = "(select t2.仓库,t2.编号,t2.总库存数 as 门店库存,t2.寄存数 as 门店寄存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    '    sqlC = "(select t3.仓库,t3.编号,t3.总库存数 as 仓库库存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    '    sqlD = "(select t4.门店名称,t4.商品编号,t4.实际数量 as 上月销量 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    '    sqlE = "(select t5.门店名称,t5.商品编号,t5.实际数量 as 本月销量 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
    '    sqlA = "(select * from [补货明细$]) t1"
    '    sqlB = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    '    sqlC = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    '    sqlD = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    '    sqlE = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
   
    SQL = "select * from (((" & _
    sqlA & " left join " & _
    sqlB & " on t2.仓库=t1.补货仓库 and t2.编号= t1.商品编号) left join " & _
    sqlC & " on t3.编号=t1.商品编号) left join " & _
    sqlD & " on t4.门店名称=t1.补货仓库 and t4.商品编号=t1.商品编号) left join " & _
    sqlE & " on t5.门店名称=t1.补货仓库 and t5.商品编号=t1.商品编号"
    Debug.Print SQL
    If rs.State = adStateOpen Then rs.Close
    rs.Open SQL, conn, 1, 3
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs.RecordCount
    Debug.Print rs.Fields.Count
    '    For i = 0 To rs.Fields.Count - 1
    '        Debug.Print rs.Fields(i)
    '    Next
    Do Until rs.EOF
        Debug.Print rs("补货仓库") & "   " & rs("商品编号") & "   " & rs("门店库存") & "   " & rs("门店寄存")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

chr78228

Private Sub Command1_Click()
    Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim SQL$, sqlA$, sqlB$, sqlC$, sqlD$, sqlE$, link$, i As Long
    'link = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\My Documents\Desktop\bhTool\补货明细.xls;Extended Properties=Excel 12.0"
    link = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\Desktop\bhTool\补货明细.xls;Extended Properties='Excel 8.0';Persist Security Info=False"
    conn.Open link
    sqlA = "(select t1.补货仓库,t1.商品分类,t1.商品名称,t1.规格,t1.单位,t1.商品编号,t1.数量,t1.商品备注 from [补货明细$]) t1"
    sqlB = "(select t2.仓库,t2.编号,t2.总库存数 as 门店库存,t2.寄存数 as 门店寄存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    sqlC = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    sqlD = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    sqlE = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
    '    sqlA = "(select t1.补货仓库,t1.商品分类,t1.商品名称,t1.规格,t1.单位,t1.商品编号,t1.数量,t1.商品备注 from [补货明细$]) t1"
    '    sqlB = "(select t2.仓库,t2.编号,t2.总库存数 as 门店库存,t2.寄存数 as 门店寄存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    '    sqlC = "(select t3.仓库,t3.编号,t3.总库存数 as 仓库库存 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    '    sqlD = "(select t4.门店名称,t4.商品编号,t4.实际数量 as 上月销量 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    '    sqlE = "(select t5.门店名称,t5.商品编号,t5.实际数量 as 本月销量 from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
    '    sqlA = "(select * from [补货明细$]) t1"
    '    sqlB = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\门店库存.xls;].[门店库存$]) t2"
    '    sqlC = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\仓库库存.xls;].[总库库存$]) t3"
    '    sqlD = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\上月销售.xls;].[上月销售$]) t4"
    '    sqlE = "(select * from [excel 8.0;database=E:\My Documents\Desktop\bhTool\本月销售.xls;].[本月销售$]) t5"
   
    SQL = "select * from (((" & _
    sqlA & " left join " & _
    sqlB & " on t2.仓库=t1.补货仓库 and t2.编号= t1.商品编号) left join " & _
    sqlC & " on t3.编号=t1.商品编号) left join " & _
    sqlD & " on t4.门店名称=t1.补货仓库 and t4.商品编号=t1.商品编号) left join " & _
    sqlE & " on t5.门店名称=t1.补货仓库 and t5.商品编号=t1.商品编号"
    Debug.Print SQL
    If rs.State = adStateOpen Then rs.Close
    rs.Open SQL, conn, 1, 3
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs.RecordCount
    Debug.Print rs.Fields.Count
    '    For i = 0 To rs.Fields.Count - 1
    '        Debug.Print rs.Fields(i)
    '    Next
    Do Until rs.EOF
        Debug.Print rs("补货仓库") & "   " & rs("商品编号") & "   " & rs("门店库存") & "   " & rs("门店寄存")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub
我检查了N遍 都提示 至少有一个参数未被设定值

zpy2

chr78228 发表于 2019-2-21 01:05
Private Sub Command1_Click()
    Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
    D ...

有可能Excel表的字段名不规范，有不看见的字符导致sql语句识别不了，建议还是存到数据库比较好。

zpy2

Debug.Print SQL
你可以先分别测试一下 a,b,c,d个别的语句，然后整合起来，还有一种就是连接的字段 类型不一样 用cstr转一下，比如，cstr(仓库)=cstr(trim(补货仓库))

chr78228

谁能帮我检查一下 问题出在哪里?谢谢了

chr78228

谁能帮我检查一下 问题出在哪里?

opiona

1. Private Sub Command1_Click()
   
2.     Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
   
3.     Dim SQL$, sqlA$, sqlB$, sqlC$, sqlD$, sqlE$, link$, i As Long
   
4.    
   
5.     Rem 连接Excel字符串 和连接Access不同
   
6.     link = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & App.Path & "\DATA\补货明细.xls"
   
7.     conn.Open link
   
8.    
   
9.     Rem SQL写法习惯大家都不一样
   
10.     SQL = "select t1.补货仓库,t1.商品分类,t1.商品名称,t1.规格,t1.单位,t1.商品编号,t1.数量,t1.商品备注"
    
11.     SQL = SQL & ",t2.总库存数"
    
12.     SQL = SQL & " from ("
    
13.     SQL = SQL & " select 补货仓库,商品分类,商品名称,规格,单位,商品编号,数量,商品备注 from [补货明细$] "
    
14.     SQL = SQL & ") AS t1 left join ("
    
15.     SQL = SQL & " select * from [excel 12.0;database=" & App.Path & "\DATA\门店库存.xls].[门店库存$]"
    
16.     SQL = SQL & ") t2 on t2.仓库=t1.补货仓库 and t2.编号=t1.商品编号"
    
17.    
    
18.     If rs.State = adStateOpen Then rs.Close
    
19.     rs.Open SQL, conn, 1, 3
    
20.     For i = 1 To rs.RecordCount
    
21.         Rem 将数据赋值给控件
    
22.         If IsNull(rs.Fields("补货仓库")) = False Then Combo1.AddItem rs.Fields("补货仓库")
    
23.         If IsNull(rs.Fields("商品编号")) = False Then Combo2.AddItem rs.Fields("商品编号")
    
24.         If IsNull(rs.Fields("总库存数")) = False Then Combo3.AddItem rs.Fields("总库存数")
    
25.         rs.MoveNext
    
26.     Next i
    
27.     rs.Close
    
28.     Set rs = Nothing
    
29.     conn.Close
    
30.     Set conn = Nothing
    
31. End Sub
    

复制代码

opiona

Form1.rar (38.29 KB, 下载次数: 12)

2019-2-21 21:19 上传

点击文件名下载附件