SQL via VBA error: Operation is not allowed when the object is closed

[Viceroy369]

Hello,

I've been trying to connect to a SQL Server database, but consistently get an "error 3704: Operation is not allowed when the object is closed" on this line:

ws1.Range("A14").CopyFromRecordset rs


The SQL query itself runs fine when I paste it into the query window of the SQL Server, so I don't think it's that.

Also, I have some code to check if the recordset is empty, and it is not empty. Since I open the recordset just before I copy it, not sure why it's saying the RS is closed.

Here is my complete code:

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
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strConn As String
Dim objMyCmd As ADODB.Command
Set objMyCmd = New ADODB.Command

Server_Name = Range("B1").Value
Database_Name = Range("B2").Value
User_ID = Range("B3").Value
Password = Range("B4").Value

Set cn = New ADODB.Connection
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & Server_Name & ";INITIAL CATALOG=" & Database_Name & ";"
strConn = strConn & "User ID=" & User_ID & "; Password=" & Password
cn.ConnectionString = strConn
cn.Open

Set objMyCmd.ActiveConnection = cn

objMyCmd.CommandText = "SELECT Time_period, Name, CASE WHEN Output_MWh > 0 THEN 1 ELSE 0 End As Availability Into #TEMP"
objMyCmd.CommandText = objMyCmd.CommandText & " FROM table1 Where Report_Year >= 2015 AND (Name LIKE 'Plant1' OR NAME LIKE 'Plant2')"
objMyCmd.CommandText = objMyCmd.CommandText & " SELECT Time_period, [Plant1], [Plant2] FROM #temp"
objMyCmd.CommandText = objMyCmd.CommandText & " PIVOT (SUM (Availability) For Name in ([Plant1], [Plant2])) AS PVT"
objMyCmd.CommandText = objMyCmd.CommandText & " Order By time_period"
objMyCmd.CommandText = objMyCmd.CommandText & " drop table #temp"
objMyCmd.CommandType = adCmdText
' Worksheets("sheet1").Range("B7") = objMycmd.commandtext =

Set rs.Source = objMyCmd
Set rs = objMyCmd.Execute
'rs.Open

ws1.Range("A14").CopyFromRecordset rs


If rs Is Nothing Then
MsgBox "Empty"
Else
MsgBox "Not empty"
End If

'Tidy up

I have also tried it this way, and the VBA errored on the same line:

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
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strConn As String

Server_Name = Range("B1").Value
Database_Name = Range("B2").Value
User_ID = Range("B3").Value
Password = Range("B4").Value
SQLStr = "SELECT Time_period, Name, CASE WHEN Output_MWh > 0 THEN 1 ELSE 0 End As Availability Into #TEMP"
SQLStr = SQLStr & " FROM resourcehour1 Where Report_Year >= 2015 AND (Name LIKE Plant1' OR NAME LIKE 'Plant2) AND Risk_Iteration = 1"
SQLStr = SQLStr & " SELECT Time_period, [Plant1], [Plant2] FROM #temp"
SQLStr = SQLStr & " PIVOT (SUM (Availability) For Name in ([Plant1], [Plant2])) AS PVT"
SQLStr = SQLStr & " Order By time_period"
SQLStr = SQLStr & " drop table #temp"
' Worksheets("sheet1").Range("B7") = SQLStr =>I used this to copy the output query directly into the query builder in my SQL application, and it ran 'OK. Thus I think it's not the query causing the problem.
Set cn = New ADODB.Connection

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & Server_Name & ";INITIAL CATALOG=" & Database_Name & ";"
strConn = strConn & "User ID=" & User_ID & "; Password=" & Password

cn.ConnectionTimeout = 1600
cn.CommandTimeout = 180
cn.Open strConn

rs.Open SQLStr, cn, adOpenStatic

If rs Is Nothing Then
MsgBox "Empty"
Else
MsgBox "Not empty"
End If

ws1.Range("A14").CopyFromRecordset rs

'Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Any help would be much appreciated!

 

[Kyle123]

OKay, another angle - does the logged in user (from Excel) have permissions to create and drop tables? Secondly, do you have the same issue if you wrap the sql in a stored proc and execute that from Excel?

 

[Viceroy369]

Sorry for the long delay and thanks, everyone, for your help.

I've tried testing various things and I only get the error (which indicates that the recordset is closed) when I am creating the temptable. If I don't create the temptable, then the VBA (and SQL) execute fine.

I know the SQL syntax is OK because I have literally outputted the SQLStr variable (which holds my query) into the worksheet and pasted it into my query builder in the SQL interface, and it runs OK.

Any reason why creating the temptable would cause issues? And is there an easy way to edit my query to get the same result without a temptable? (A subquery doesn't seem to work, perhaps because of the pivot function in my main query.)

Here's the query again:

SQLStr = "SELECT Time_period, Name, CASE WHEN Output_MWh > 0 THEN 1 ELSE 0 End As Availability into #TEMP"
SQLStr = SQLStr & " FROM resourcehour1 Where Report_Year >= 2015 AND (Name LIKE 'Plant1' OR NAME LIKE 'Plant2') AND Risk_Iteration = 1"
SQLStr = SQLStr & " SELECT Time_period, [Plant1], [Plant2] FROM #temp"
SQLStr = SQLStr & " PIVOT (SUM (Availability) For Name in ([Plant1], [Plant2])) AS PVT"
SQLStr = SQLStr & " Order By time_period"
SQLStr = SQLStr & " drop table #TEMP"

 

[Viceroy369]

OK, I got it to work, and am posting for future users (and for those who helped on this thread). Hopefully it will come in handy for someone.

The issue is that creating and dropping tables does not involve a recordset, so I needed to use the execute method of the connection to make it work. I broke down the query into various subparts which, while not efficent, did end up returning the right result. So this is a case when the recordset being closed was a bit misleading and the solution was not obvious.

Here is the code:

cn.Open strConn
SQLStr = " drop table #temp"
On Error Resume Next
cn.Execute SQLStr

SQLStr = "set nocount on SELECT Time_period, Name, CASE WHEN Output_MWh > 0 THEN 1 ELSE 0 End As Availability Into #TEMP"
SQLStr = SQLStr & " FROM resourcehour1 Where Report_Year >= 2015 AND (Name LIKE 'Plant1' OR NAME LIKE 'Plant2') AND Risk_Iteration = 1"
cn.Execute SQLStr
SQLStr = " SELECT Time_period, [Plant1], [Plant2] FROM #temp"
SQLStr = SQLStr & " PIVOT (SUM (Availability) For Name in ([Plant1], [Plant2])) AS PVT"
SQLStr = SQLStr & " Order By time_period"
rs.Open SQLStr, cn, adOpenStatic
SQLStr = " drop table #temp"
cn.Execute SQLStr
ws1.Range("A15").CopyFromRecordset rs
rs.Close
Set rs = Nothing

 

[xenou]

Thanks for the info. Good job.

 

[gdooling]

OK, I got it to work, and am posting for future users (and for those who helped on this thread). Hopefully it will come in handy for someone.

The issue is that creating and dropping tables does not involve a recordset, so I needed to use the execute method of the connection to make it work. I broke down the query into various subparts which, while not efficent, did end up returning the right result. So this is a case when the recordset being closed was a bit misleading and the solution was not obvious.

Here is the code:

cn.Open strConn
SQLStr = " drop table #temp"
On Error Resume Next
cn.Execute SQLStr

SQLStr = "set nocount on SELECT Time_period, Name, CASE WHEN Output_MWh > 0 THEN 1 ELSE 0 End As Availability Into #TEMP"
SQLStr = SQLStr & " FROM resourcehour1 Where Report_Year >= 2015 AND (Name LIKE 'Plant1' OR NAME LIKE 'Plant2') AND Risk_Iteration = 1"
cn.Execute SQLStr
SQLStr = " SELECT Time_period, [Plant1], [Plant2] FROM #temp"
SQLStr = SQLStr & " PIVOT (SUM (Availability) For Name in ([Plant1], [Plant2])) AS PVT"
SQLStr = SQLStr & " Order By time_period"
rs.Open SQLStr, cn, adOpenStatic
SQLStr = " drop table #temp"
cn.Execute SQLStr
ws1.Range("A15").CopyFromRecordset rs
rs.Close
Set rs = Nothing

Thank you, thank you, thank you! I had to register for Mr. Excel just to tell you thanks! This was driving me crazy. I could get mine to work when I did a test

Select top 10 * FROM myTable

but I couldn't get it to work with the code that had the temp table.

 

[raiyanb]

Just to add to this I had the same problem but it went when I removed the USE DATABASE; part of the query in the following example

"USE DATABASE1; WITH SQ AS (SELECT....."