3704 Operation is not allowed when the object is closed

[danny2001k]

Hello,

I have a standard vba code for getting data from a SQL Server

The problem is that if I run a simple Query like "Select * from database.int_hour " it works but when I try a more complicated query with temp tables I get the error: 3704 Operation is not allowed when the object is closed.

The error apears at the line : ".CopyFromRecordset rs1"

The user has the required rights for the database, and I can run the query directly on the sql server.

Any help would be appreciated.

Ty,
Danny

Sub bschour()
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String

    srv = "server"
    dtb = "databese"
    usr = "usr"
    pass = "pa$$word!"

   SQLStr = "SELECT * into ##temp1 from database.int_hour select * from ##temp1"
    
    Set Cn = New ADODB.Connection
    If Cn.State = adStateOpen Then Cn.Close
    Cn.Open "Driver={SQL Server};Server=" & srv & ";Database=" & dtb & _
    ";Uid=" & usr & ";Pwd=" & pass & ";"
     
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
     
    rs1.Open SQLStr, Cn, adOpenStatic
    
     ' Write in excel
    With Worksheets("BSC Hour").Range("a6:o500")
        .ClearContents
        .CopyFromRecordset rs1
    End With


    çlear data
    rs1.Close
    Set rs1 = Nothing
    Cn.Close
    Set Cn = Nothing

End Sub

 

[RoryA]

If you do anything other than a SELECT operation you need to use SET NOCOUNT ON