Excel VBA ADO SQL: error de sintaxis en la cláusula From
El siguiente SQL en VBA ADO da el error 'error de sintaxis en la cláusula From'.
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
El debug.print de strSQL es:
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;
Cuando uso una cadena strSQL simple, la conexión funciona y devuelve resultados. Esto funciona:
strSQL = "SELECT * FROM [" & strRangeAddress & "]"
Creo que tengo la sintaxis correcta. ¿Es posible que el error surja de SQL no compatible? Por ejemplo, ¿puede ADO hacer 'Partición por'?
Estoy usando Excel 2010 de 64 bits de Office.