如何将SQL查询语句直接写入VBA代码，而不用建立辅助的查询表

Jack　发表于：2015-12-30 10:52:36 　

复制

如何将SQL查询语句直接写入VBA代码，而不用建立辅助的查询表？？？

查询：QryPbZone

QryPbZone：如何将此SQL查询语句直接写入VBA代码，无需借用此查询表

SELECT DISTINCT Left([PlID],5) AS 区域ID,

DLookUp("PzName","PbZone","PzID='" & Right([区域ID],3) & "'") AS 区域名称,

DLookUp("PtID","PbType","Right([PtID],2)='" & Left([区域ID],2) & "'") AS 属性ID,

DLookUp("PtName","PbType","Right([PtID],2)='" & Left([区域ID],2) & "'") AS 属类名称

FROM PjList
WHERE (((Left([PlID],5))<>"0"))
ORDER BY Left([PlID],5);

Private Sub TreeLoad()
Dim nodeindex As Node

Dim strSQL as String
Dim Rec As New ADODB.Recordset
Dim i As Integer
'设置爷
Set nodeindex = TVInput.Nodes.Add(, , "爷", "项目列表(List)", "K1", "K2")
nodeindex.Sorted = False
'设置父
Rec.Open "SELECT * FROM PbType ORDER BY PtID;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
For i = 0 To Rec.RecordCount - 1
Set nodeindex = TVInput.Nodes.Add("爷", tvwChild, "父" & Rec.Fields("PtID"), Rec.Fields("PtName"), "K3", "K4")
nodeindex.Sorted = False
Rec.MoveNext
Next i
Rec.Close
'设置子
'* ---------------------------

strSQL="查询QryPbZone的SQL语句"：如何利用字符串变量strSQL，将SQL语句直接写入VBA代码，让Rec.Open"strSQL"
Rec.Open "QryPbZone", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
For i = 0 To Rec.RecordCount - 1
Set nodeindex = TVInput.Nodes.Add("父" & Rec.Fields("属性ID"), tvwChild, "子" & Rec.Fields("区域ID"), Rec.Fields("区域名称"), "K5", "K6")
nodeindex.Sorted = False
Rec.MoveNext
Next
Rec.Close
'设置孙
'* ---------------------------
Rec.Open "QryPjList", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
For i = 0 To Rec.RecordCount - 1
Set nodeindex = TVInput.Nodes.Add("子" & Left(Rec.Fields("PlID"), 5), tvwChild, "孙" & Rec.Fields("PlID"), Rec.Fields("PlName"), "K7", "K8")
nodeindex.Sorted = False
Rec.MoveNext
Next
Rec.Close
End Sub

点击下载此附件

Top

Jack 发表于：2015-12-30 14:18:58

SQL语句

VBA代码

报错提示

Top

煮江品茶发表于：2016-01-02 10:04:07

双引号错误嘛。

试试：
select DISTINCT Left(a.[PlID],5) AS 区域ID,b.PzName AS 区域名称,c.PtID AS 属性ID,c.PtName as 属类名称
from PjList as a
inner join PbZone as b on b.PzID=mid(a.PlID,2,3)
inner join PbType as c on Right(c.PtID,2)=left(a.PlID,2)

参见：http://www.accessoft.com/article-show.asp?id=10118

Top

Jack 发表于：2016-01-08 10:00:26

煮江品茶老师，我按照您上述代码去写，结果错位！！！请指教指正

Top

Jack 发表于：2016-01-08 10:20:47

a单独与b链接：ok

select DISTINCT Left(a.[PlID],5) AS 区域ID,b.PzName AS 区域名称
from PjList as a
inner join PbZone as b on b.PzID=MID(a.[plID],3,3)
a单独与c链接：ok

select DISTINCT Left(a.[PlID],5) AS 区域ID,c.PtID AS 属性ID,c.PtName as 属类名称
from PjList as a
inner join PbType as c on Right(c.PtID,2)=left(a.PlID,2)
然而，a同时与b和c链接，就不对了：

select DISTINCT Left(a.[PlID],5) AS 区域ID,b.PzName AS 区域名称,c.PtID AS 属性ID,c.PtName as 属类名称
from PjList as a
inner join PbZone as b on b.PzID=MID(a.[plID],3,3)
inner join PbType as c on Right(c.PtID,2)=left(a.PlID,2)
请指教？？？？

Top

Jack 发表于：2016-01-08 10:33:20

最终正确答案：

select DISTINCT Left(a.PlID,5) AS 区域ID,b.PzName AS 区域名称,c.PtID AS 属性ID,c.PtName as 属类名称

from (PjList as a inner join PbZone as b on b.PzID=MID(a.plID,3,3))

inner join PbType as c on Right(c.PtID,2)=left(a.PlID,2)

参阅资料：

1、SQL语句（三）INNER JOIN ... ON用法http://http://www.accessoft.com/article-show.asp?id=6592

2、sql语句（二）inner joinhttp://http://www.accessoft.com/article-show.asp?id=6585

Top

总记录：5篇页次：1/1 9 1 :

Access交流中心

如何将SQL查询语句直接写入VBA代码，而不用建立辅助的查询表