With Excel VBA, trying to use a Join AFTER Union in SQL-CodePudding

I have a few different csv files that I need to stack so I ran a union. After they are done, I set them as E and tried to join an xlsx file as f. I am able to successfully union the csv files with no issues. I am able to successfully load in the xlsx file without issue as well. When I combine them, however, I get the error "Syntax error in the FROM clause". I am usually able to troubleshoot it with enough research but this one may be the one that finally has got me.

Please note the names of files have been changed for security purposes.

The exact error I receive is the following:

Run-time error '-2147467259 (80004005)':
Syntax error in FROM clause.

Below is my code. Note that both files, independently, pull perfectly and the columns are populated with expected data. It is when I try to combine them with a join function that I receive this error.

' Set files
    Path = "C:\Automation\Location\Files\"
    FTE_file = "C:\Automation\Location\Files\FTE.xlsx"
    
    Blah = "Blah.csv"
    Blah_Int = "Blah Internal.csv"
    
    Blerg = "Blerg.csv"
    Blerg_Int = "Blerg Internal.csv"
   
   
   ' Create the connection string.
    sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                   & "Data Source=" & Path & ";" _
                   & "Extended Properties=""text;HDR=No;FMT=Delimited;"";"


   ' Create the Connection and Recordset objects.
   Set conn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   
   sql = "SELECT e.[F1], e.[F10], e.[F11], e.[F13], e.[F16], e.[F17], Code " & _
   "FROM ((( " & _
   "SELECT [F1], [F10], [F11], [F13], [F16], [F17], 1 AS Code " & _
   "FROM [" & CCS & "] " & _
   "UNION " & _
   "SELECT [F1], [F10], [F11], [F13], [F15], [F16], 2 AS Code " & _
   "FROM [" & CCS_Int & "] " & _
   "UNION " & _
   "SELECT [F1], [F10], [F11], [F12], [F15], [F16], 3 AS Code " & _
   "FROM [" & SPS & "] " & _
   "UNION " & _
   "SELECT [F1], [F10], [F11], [F12], [F14], [F15], 4 AS Code " & _
   "FROM [" & SPS_Int & "] ) AS e ) " & _
   "LEFT JOIN " & _
   "(SELECT * FROM [Excel 12.0 Xml;HDR=No;" & _
   "Database=" & FTE_file & ";Readonly=False].[FTE$]) AS f " & _
   "ON e.[F10] = f.[F2] ) "


   ' Open the connection and execute.
   conn.Open sConnString
   Set rs = conn.Execute(sql)
   
   
   ' Lets pull it
   Worksheets("All Data").Activate
    
   ActiveSheet.Range("A2").CopyFromRecordset rs
   rs.Close
   conn.Close

As requested, I ran Debug.Print sql

SELECT e.[F1], e.[F10], e.[F11], e.[F13], e.[F16], e.[F17], Code 
FROM ((( 
SELECT [F1], [F10], [F11], [F13], [F16], [F17], 1 AS Code 
FROM [CCS Satisfaction Survey.csv] 
UNION 
SELECT [F1], [F10], [F11], [F13], [F15], [F16], 2 AS Code 
FROM [CCS Satisfaction Survey Internal.csv] 
UNION 
SELECT [F1], [F10], [F11], [F12], [F15], [F16], 3 AS Code 
FROM [SPS Satisfaction Survey.csv] 
UNION 
SELECT [F1], [F10], [F11], [F12], [F14], [F15], 4 AS Code 
FROM [SPS Satisfaction Survey Internal.csv] ) AS e ) 
LEFT JOIN (
SELECT * 
FROM [Excel 12.0 Xml;HDR=No;Database=C:\Automation\NPS Score Card\Files\FTE.xlsx;Readonly=False].[FTE$]) AS f 
ON e.[F10] = f.[F2] )

CodePudding user response：

I will leave it up to @parfait (more knowledge in ms-access and vba then I have), but:

The SQL query currently looks like:

SELECT e.[F1], e.[F10], e.[F11], e.[F13], e.[F16], e.[F17], Code 
FROM (
      (     <==MARKED
       ( 
         SELECT [F1], [F10], [F11], [F13], [F16], [F17], 1 AS Code 
         FROM [CCS Satisfaction Survey.csv] 
         UNION 
         SELECT [F1], [F10], [F11], [F13], [F15], [F16], 2 AS Code 
         FROM [CCS Satisfaction Survey Internal.csv] 
         UNION 
         SELECT [F1], [F10], [F11], [F12], [F15], [F16], 3 AS Code 
         FROM [SPS Satisfaction Survey.csv] 
         UNION 
         SELECT [F1], [F10], [F11], [F12], [F14], [F15], 4 AS Code 
         FROM [SPS Satisfaction Survey Internal.csv] 
       ) AS e 
      )    <==MARKED
LEFT JOIN (
            SELECT * 
            FROM [Excel 12.0 Xml;HDR=No;Database=C:\Automation\NPS Score Card\Files\FTE.xlsx;Readonly=False].[FTE$]
          ) AS f 
ON e.[F10] = f.[F2] 
     )

I think the () which are marked (with "<==MARKED") in the above SQL statement, should be removed.

@Vicotor-g: Do not edit anything, wait for reaction from parfait

This message will self-desctruct in 13-14 hours.....