弱弱问一句：请问如何调用ACCESS里面的查询？

somepeople

在论坛里学习了调用ACCESS里面的表，如下语句，但查询是怎么表示呢？
Sub 查询1()
    Dim myconnect As ADODB.Connection
    Dim myres As ADODB.Recordset
    Dim mydata As String, mySQL As String
    mydata = "F:\业绩认定程\数据库.mdb"
    Set myconnect = New ADODB.Connection
    myconnect.ConnectionString = "provider=microsoft.jet.oledb.4.0;" _
                               & "data source=" & mydata
    myconnect.Open
    Set myres = New ADODB.Recordset
    mySQL = "select * from 生成个人数据"                '生成个人数据  是一个数据表，但不知道如何调用查询？请教了！
    myres.Open mySQL, myconnect, adOpenKeyset, adLockOptimistic
    For icols = 0 To myres.Fields.Count - 1
    Sheet2.Cells(1, icols + 1).Value = myres.Fields(icols).Name
    Next icols
    Sheet2.Cells(2, 1).CopyFromRecordset myres
    myres.Close
    myconnect.Close
    Set myres = Nothing
    Set myconnect = Nothing
End Sub

zhaogang1960

好像和调用数据表一样，举例：如果数据库db1中有A、B两个数据表，现创建一个查询——A 与 B 不匹配

1. Sub 查询1()
   
2.     Dim myconnect As ADODB.Connection
   
3.     Dim myres As ADODB.Recordset
   
4.     Dim mydata As String, mySQL As String
   
5.     mydata = ThisWorkbook.Path & "\db1.mdb"
   
6.     Set myconnect = New ADODB.Connection
   
7.     myconnect.Open "provider=microsoft.jet.oledb.4.0;data source=" & mydata
   
8.     Set myres = New ADODB.Recordset
   
9.     mySQL = "select * from [A 与 B 不匹配]"                'A 与 B 不匹配  是一个查询
   
10.     myres.Open mySQL, myconnect, adOpenKeyset, adLockOptimistic
    
11.     For icols = 0 To myres.Fields.Count - 1
    
12.     Cells(1, icols + 1).Value = myres.Fields(icols).Name
    
13.     Next icols
    
14.     Cells(2, 1).CopyFromRecordset myres
    
15.     myres.Close
    
16.     myconnect.Close
    
17.     Set myres = Nothing
    
18.     Set myconnect = Nothing
    
19. End Sub
    

复制代码

zhaogang1960

请看附件
ADO调用Access查询.rar (44.44 KB, 下载次数: 42)

2013-4-9 17:05 上传

点击文件名下载附件

somepeople

zhaogang1960 发表于 2013-4-9 17:05
请看附件

再次劳驾版主的热心回复。先下来学习

somepeople

抱歉，按版主的格式，我修改了查询的名称，但运行结果只有表头，未见内容。现上传附件，劳烦高人指点。谢谢了。

zhaogang1960

somepeople 发表于 2013-4-9 23:49
抱歉，按版主的格式，我修改了查询的名称，但运行结果只有表头，未见内容。现上传附件，劳烦高人指点。谢谢 ...

1. Sub 查询1()
   
2.     Dim myconnect As ADODB.Connection
   
3.     Dim myres As ADODB.Recordset
   
4.     Dim mydata As String, mySQL As String
   
5.     mydata = ThisWorkbook.Path & "\数据库.mdb"
   
6.     Set myconnect = New ADODB.Connection
   
7.     myconnect.Open "DBQ=" & mydata & ";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
   
8.     Set myres = New ADODB.Recordset
   
9.    ' mySQL = "select * from [A 与 B 不匹配]"                'A 与 B 不匹配  是一个查询
   
10.     mySQL = "select * from 黄"
    
11.     myres.Open mySQL, myconnect, adOpenKeyset, adLockOptimistic
    
12.     MsgBox myres.RecordCount
    
13.     For icols = 0 To myres.Fields.Count - 1
    
14.     Cells(1, icols + 1).Value = myres.Fields(icols).Name
    
15.     Next icols
    
16.     Cells(2, 1).CopyFromRecordset myres
    
17.     myres.Close
    
18.     myconnect.Close
    
19.     Set myres = Nothing
    
20.     Set myconnect = Nothing
    
21. End Sub
    

复制代码

zhaogang1960

这个查询使用原来的OLEDB驱动查不到，改用ODBC驱动可以
请看附件
ADO调用Access查询1.rar (30.08 KB, 下载次数: 40)

2013-4-10 01:15 上传

点击文件名下载附件

somepeople

zhaogang1960 发表于 2013-4-10 01:15
这个查询使用原来的OLEDB驱动查不到，改用ODBC驱动可以
请看附件

感谢，能说说为什么改用ODBC驱动可以，原来的却查不到？

somepeople

somepeople 发表于 2013-4-10 08:09
感谢，能说说为什么改用ODBC驱动可以，原来的却查不到？

呵呵，好的，谢谢！

icepoint223

zhaogang1960 发表于 2013-4-10 01:15
这个查询使用原来的OLEDB驱动查不到，改用ODBC驱动可以
请看附件

老师，你好，我按照你的说发，去查询，也只能查询到表头，后来我发现我把查询的SQL语句写简单点就能查出值来。这是为什么
复杂语句不行
SELECT f.利润中心, f.员工姓名, Sum(f.补助) AS 补助, Sum(f.长途交通) AS 长途交通, Sum(f.出租车) AS 出租车, Sum(f.公交地铁) AS 公交地铁, Sum(f.机票) AS 机票, Sum(f.住宿) AS 住宿
FROM (SELECT corp_name,period, mid(subj_name,1,instr(subj_name,'/')-1) AS 利润中心 , mid(subj_name,instr(subj_name,'/')+1) AS 员工姓名 , iif(subj_no = '6601.07.06',jfs,0) AS 补助 , iif(subj_no = '6601.07.03',jfs,0) AS 长途交通 , iif(subj_no = '6601.07.04',jfs,0) AS 出租车 , iif(subj_no = '6601.07.05',jfs,0) AS 公交地铁 , iif(subj_no = '6601.07.02',jfs,0) AS 机票 , iif(subj_no = '6601.07.01',jfs,0) AS 住宿  FROM ye_b AS b WHERE b.subj_no like '6601.07*' and b.subj_name not in ('差旅费','住宿费','机票款','高铁/长途汽车','的士/出租车','公交/地铁','补贴')   union all  SELECT corp_name,period, mid(subj_name,1,instr(subj_name,'/')-1) AS 利润中心 , mid(subj_name,instr(subj_name,'/')+1) AS 员工姓名 , iif(subj_no = '6602.07.06',jfs,0) AS 补助 , iif(subj_no = '6602.07.02',jfs,0) AS 长途交通 , iif(subj_no = '6602.07.04',jfs,0) AS 出租车 , iif(subj_no = '6602.07.05',jfs,0) AS 公交地铁 , iif(subj_no = '6602.07.03',jfs,0) AS 机票 , iif(subj_no = '6602.07.01',jfs,0) AS 住宿  FROM ye_b AS b WHERE b.subj_no like '6602.07*' and b.subj_name not in ('差旅费','住宿费','机票款','高铁/长途汽车','的士/出租车','公交/地铁','补贴')     )  AS f
WHERE (((f.补助+f.长途交通+f.出租车+f.公交地铁+f.机票+f.住宿)<>0) And ((f.corp_name)='金业文化传播有限公司') And ((f.period)='04'))
GROUP BY f.利润中心, f.员工姓名
ORDER BY f.员工姓名;

简单语句可以


SELECT * from ye_b where period='01' and corp_name= '金业文化传播有限公司'