Die folgende SQL in VBA ADO gibt den Fehler "Syntaxfehler in From-Klausel".

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

Die debug.print von strSQL ist:

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;

Wenn ich einen einfachen strSQL-String verwende, funktioniert die Verbindung und es werden Ergebnisse zurückgegeben. Das funktioniert

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

Ich glaube, ich habe die richtige Syntax. Ist es möglich, dass der Fehler auf nicht kompatibles SQL zurückzuführen ist? Kann ADO beispielsweise "Partition by" ausführen?

ch verwende Excel 2010 64-Bit-Offic