Excel VBA ADO SQL-From子句中的语法错误

本文介绍了Excel VBA ADO SQL-From子句中的语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 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的要求： 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.GetStringEnd SubThe 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子句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！