VBA Code for create table in SQL

Hi

below is my Excel VBA code. When I am running simple select query the same is working fine. However when execute a multiline SQL (which includes creation of temp table, then filtering the results and then dropping the table etc) it throws an error. Something like 'Application Defined or Object Defined Error'.

Please help me with this as soon as possible. Thank you so much in Advance. Please see my code below:

Sub ExtractDatafrSQL()

Dim Cn As ADODB.Connection

Dim SQLStr As String
Dim mySQl As Object
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim oQt As QueryTable

SQLStr = "use [migration_mtg_bau_db]"
SQLStr = SQLStr & " create table #ByProject ([ProjectNo] varchar(50), ApplicationName varchar(50), Priority varchar(50), [ExecutionStatus] varchar(50), Number int)"
SQLStr = SQLStr & " insert into #ByProject ([ProjectNo],ApplicationName, Priority, [ExecutionStatus], Number)"
SQLStr = SQLStr & " select ts_User_07,ts_user_05, ts_user_09, ts_exec_status, count(ts_exec_status)from td.Test"
SQLStr = SQLStr & " where ts_user_07 in ('P0018075', 'P0019812', 'P0019922', 'P0019947', 'P0019974', 'P0019975', 'P0020085', 'P0020275', 'P0020291','P0020380', 'P0020545', 'P0020620', 'P0020679') and ts_user_01 in ('R2.09', '2.09')"
SQLStr = SQLStr & " group by ts_User_07, ts_user_05, ts_user_09, ts_exec_status"
'Filter By Project No
SQLStr = SQLStr & " SELECT ProjectNo, Priority,"
SQLStr = SQLStr & " SUM(Number) AS Total,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Passed' THEN Number ELSE 0 END) AS Passed,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Failed' THEN Number ELSE 0 END) AS Failed,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='No Run' THEN Number ELSE 0 END) AS Pending,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Not Completed' THEN Number ELSE 0 END) AS NotCompleted"
SQLStr = SQLStr & " FROM #ByProject"
SQLStr = SQLStr & " GROUP BY ProjectNo,Priority"
SQLStr = SQLStr & " order by ProjectNo"
'SQLStr = SQLStr & "Where ts_user_09 in ('1-High','2-Medium','3-Low')"
Set Cn = New ADODB.Connection
'Print Cn
'Cn.Open "Provider=SQLOLEDB; Datasource=t01sql01; Initial Catalogue=migration_mtg_bau_db;User Id=vpatlol;Password=Venu@0681"

Cn.Open "Provider=sqloledb;" & _
"Data Source=t01sql01;" & _
"Initial Catalog=migration_mtg_bau_db;" & _
"Integrated Security=SSPI"

' Set oQt = ActiveSheet.QueryTables.Add(Connection:=Cn, Destination:=Range("a1"), Sql:=SQLStr)
' oQt.Refresh

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z1000") ' Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub