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

Viceroy369

New Member
Joined
Jan 7, 2013
Messages
25
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Viceroy369

New Member
Joined
Jan 7, 2013
Messages
25
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

New Member
Joined
Jan 7, 2013
Messages
25
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the info. Good job. (y)
 

gdooling

New Member
Joined
Jan 28, 2014
Messages
5
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
Code:
Select top 10 * FROM myTable
but I couldn't get it to work with the code that had the temp table.
 

raiyanb

New Member
Joined
Nov 22, 2013
Messages
10
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....."
 

Watch MrExcel Video

Forum statistics

Threads
1,127,270
Messages
5,623,737
Members
415,986
Latest member
C_Braga

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top