VBA SQL: Runtime Error 3704, how can I fix this?

[mgd650]

Ok , so I know there are SO MANY examples of this already but none of the solutions have helped me so far.

I am trying to run a SQL Query through VBA. (I'm new to VBA)

I have copied the code from another excel (which works flawlessly) but my SQL Query involves temp tables and the other doesn't (this used to work on previous files that had temp tables). For some reason it just fails and I get the following error on the following line

Error: 'Error: 3704 Operation is not allowed when the object is closed'

 ' Check we have data for OrderIDs.
    If Not rsfswdata.EOF Then

Below is my full VBA Code

Dim conn As ADODB.Connection
    Dim rsfswdata As ADODB.Recordset
    Dim sConnString As String
    
    
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=server001;" & _
                  "Initial Catalog=Dev;" & _
                  "Integrated Security=SSPI;"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'To wait till the query finishes without generating error
    conn.ConnectionTimeout = 0
    
    'To wait till the query finishes without generating error
    conn.CommandTimeout = 0
    
    ' Open the connection and execute.
    conn.Open sConnString
    
   
    Set rsfswdata = conn.Execute(Sheets("SQL Query").Range("A1").Value)
    
    ' Check we have data for OrderIDs.
    If Not rsfswdata.EOF Then
        ' Transfer result.
        Sheets("test").Cells(3, 2).CopyFromRecordset rsfswdata
    ' Close the recordset
        rsfswdata.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    
  

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rsfswdata = Nothing
End Sub