Excel VBA ADO SQL-From子句中的语法错误 [英] Excel VBA ADO SQL - Syntax error in From clause

问题描述

VBA ADO中的以下SQL给出 From子句中的语法错误错误。

  Sub RunSQL2（）
 
 Dim cn作为ADODB.Connection 
 Dim rs作为ADODB.Recordset 
 Dim strFile作为字符串
 Dim strCon作为字符串
 Dim strSQL作为字符串
 Dim ws作为工作表
 Dim strRangeAddress作为字符串
 Dim dataRange作为范围
 
 strFile = ThisWorkbook.Path&  \和ThisWorkbook.Name 
 strCon = Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& strFile _ 
& ; Extended Properties = Excel 8.0; HDR = Yes; IMEX = 1; 
 
设置cn = CreateObject（ ADODB.Connection）
设置rs = CreateObject（ ADODB.Recordset）
 
 cn.Open strCon 
 
 Set ws = ThisWorkbook.Sheets（ mydata）
 strRangeAddress = ActiveSheet.Name&  $& ws.Range（ A1：C30020）。Address（False，False）
 
 strSQL = strSQL& （从 
 strSQL = strSQL&中选择s。*（从（& strRangeAddress&中按（child_level顺序划分child_index，child_level选择（t。*，row_number（））[rownum] ] t）s 
 strSQL = strSQL&其中[rownum] = 1）u 
 strSQL = strSQL& 加入（从[& strRangeAddress&] t2中选择[rownum]作为t2。*，1）v 
 strSQL = strSQL& 在（v.parent_level = u.child_level和v。[rownum] = u。[rownum]）上 
 strSQL = strSQL& 联合选择w.child_index，w.child_level，w.child_level，w.child_index 
 strSQL = strSQL&来自[& strRangeAddress& ] w 
 strSQL = strSQL& 其中w.child_index = 1 
 strSQL = strSQL& 按v.child_index排序； 
 
 
 rs.Open strSQL，cn 
 
 Debug.Print rs.GetString 
 
 End Sub 
  

strSQL的debug.print是：

 从v.child_index，v.child_level，v.parent_level，u.child_index中选择作为父索引
从
中选择（从s。* 
从
中选择（选择t。*， row_number（）超过（按child_index，child_level按child_level顺序划分）[rownum] 
 from [mydata $ A1：C30020] t 
）s 
其中[rownum] = 1 
 ）u 
加入
（选择t2。*，从[mydata $ A1：C30020] t2 
中选择[rownum] 
为1）v on（v.parent_level = u.child_level和v。[rownum] = u。[rownum]）
 
工会
 
选择w.child_index，w.child_level，w.child_level，w.child_index 
从[mydata $ A1：C30020] w 
，其中w.child_index = 1 
由v.child_index排序； 
  

当我使用简单的strSQL字符串时，连接有效并返回结果。可行：

  strSQL = SELECT * FROM [& strRangeAddress& ] 
  

我认为我的语法正确。错误是否可能源于不兼容的SQL？例如，ADO可以执行分区依据吗？

我正在使用Excel 2010 64位Office。

解决方案

考虑使用相关计数聚合查询代替窗口函数，因为此子查询应符合Jet / ACE SQL的要求：

< pre $ = lang-sql prettyprint-override> SELECT v.child_index，v.child_level，v.parent_level，u.child_index，v.parent_index
FROM
（SELECT s 。*
FROM
（选择t。*，（SELECT count（*）从[mydata $ A1：C30020] sub
WHERE sub.child_index< = t.child_index
AND sub.child_level = t.child_level）as [rownum]
FROM [mydata $ A1：C30020] t
）s
WHERE [rownum] = 1
）u
内部联接
（选择t2。*，从[mydata $ A1：C30020] t2
以1作为[rownum]
）v
ON（v.parent_level = u。 child_level）AND（v。[rownum] = u。[rownum]）

UNION

SELECT w.child _index，w.child_level，w.parent_level，w.child_index，w.parent_index
FROM [mydata $ A1：C30020] w
W.child_index = 1
OR BY BY v.child_index;


The following SQL in VBA ADO gives the 'syntax error in From clause' error.

Sub RunSQL2()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    Dim ws As Worksheet
    Dim strRangeAddress As String
    Dim dataRange As Range

    strFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    Set ws = ThisWorkbook.Sheets("mydata")
    strRangeAddress = ActiveSheet.Name & "$" & ws.Range("A1:C30020").Address(False, False)

        strSQL = strSQL & " (select s.* from "
strSQL = strSQL & " (select t.*, row_number() over (partition by child_level order by child_index,child_level) [rownum] from [" & strRangeAddress & "] t) s "
strSQL = strSQL & " where [rownum] = 1) u "
strSQL = strSQL & " join (select t2.*, 1 as  [rownum] from [" & strRangeAddress & "] t2) v "
strSQL = strSQL & " on (v.parent_level = u.child_level and v.[rownum] = u.[rownum]) "
strSQL = strSQL & " union select  w.child_index,w.child_level,w.child_level,w.child_index "
strSQL = strSQL & " from [" & strRangeAddress & "] w "
strSQL = strSQL & " where w.child_index = 1 "
strSQL = strSQL & " order by v.child_index;"


    rs.Open strSQL, cn

    Debug.Print rs.GetString

End Sub

The debug.print of strSQL is:

select v.child_index,v.child_level,v.parent_level,u.child_index as  parent_index 
from  
  (select s.* 
   from  
     (select t.*, row_number() over (partition by child_level order by child_index,child_level) [rownum] 
      from [mydata$A1:C30020] t
     ) s  
   where [rownum] = 1
  ) u  
join 
  (select t2.*, 1 as  [rownum] 
   from [mydata$A1:C30020] t2
  ) v  on (v.parent_level = u.child_level and v.[rownum] = u.[rownum])  

union 

select  w.child_index,w.child_level,w.child_level,w.child_index  
from [mydata$A1:C30020] w  
where w.child_index = 1  
order by v.child_index;

When I use simple strSQL string the connection works and returns results. This works:

strSQL = "SELECT * FROM [" & strRangeAddress & "]"

I think I have correct syntax. Is it possible the error arises from non compatible SQL? For example can ADO do 'Partition by'?

I am using Excel 2010 64 bit Office.

解决方案

Consider using a correlated count aggregate query for row number in place of the window function as this subquery should by compliant in Jet/ACE SQL:

SELECT v.child_index, v.child_level, v.parent_level, u.child_index, v.parent_index 
FROM
  (SELECT s.* 
   FROM  
     (SELECT t.*, (SELECT count(*) FROM [mydata$A1:C30020] sub 
                   WHERE sub.child_index <= t.child_index
                   AND sub.child_level = t.child_level) as [rownum] 
      FROM [mydata$A1:C30020] t
     ) s  
   WHERE [rownum] = 1
  ) u  
INNER JOIN 
  (SELECT t2.*, 1 as [rownum] 
   FROM [mydata$A1:C30020] t2
  ) v  
ON (v.parent_level = u.child_level) AND (v.[rownum] = u.[rownum])  

UNION 

SELECT w.child_index, w.child_level, w.parent_level, w.child_index, w.parent_index 
FROM [mydata$A1:C30020] w  
WHERE w.child_index = 1  
ORDER BY v.child_index;

这篇关于Excel VBA ADO SQL-From子句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！