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!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
Your SQL is fine, it's your VBA code that's dodgy.
Viceroy hasn't reported any problems creating a connection, only in returning a recordset. Can you be more specific?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

BengalMagic

Banned user
Joined
Oct 19, 2012
Messages
141
Viceroy hasn't reported any problems creating a connection, only in returning a recordset. Can you be more specific?
He's creating the connection but his VBA code isn't opening it (they are two distinct operations).......the clue is in "operation not allowed when the object is closed".
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
BengalMagic, it is the *recordset* object that is closed and will not allow the operation. Not the connection object.
ξ
 

BengalMagic

Banned user
Joined
Oct 19, 2012
Messages
141
BengalMagic, it is the *recordset* object that is closed and will not allow the operation. Not the connection object.
ξ

If he/she is able to successfully return data directly from SSMS but not from Excel via VBA using the same SQL syntax then his/her connection is the issue. In any event, his/her method of connecting is unduly convoluted, he/she needs to keep it simple (see my code example).
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

It is in fact possible to create a connection and then have problems returning a recordset. The simplest way to demonstrate this is to send a badly formed SQL command. I do not agree that the connection code is convoluted. But I guess that's something we must agree to disagree about.
ξ


Edit: note that if there is any doubt, then simply:
Code:
cn.Open
MsgBox cn.State
An open connection should return 1
 
Last edited:

Viceroy369

New Member
Joined
Jan 7, 2013
Messages
25
It is in fact possible to create a connection and then have problems returning a recordset. The simplest way to demonstrate this is to send a badly formed SQL command. I do not agree that the connection code is convoluted. But I guess that's something we must agree to disagree about.
ξ


Edit: note that if there is any doubt, then simply:
Code:
cn.Open
MsgBox cn.State
An open connection should return 1


OK, sorry for the delay.

I do not believe the connection is an issue. When I run the above code, I do get a 1. I just don't understand why the recordset is not recognized as being open.

I will give Bengal's stuff a try to see if that helps.
 

Viceroy369

New Member
Joined
Jan 7, 2013
Messages
25

ADVERTISEMENT

I replaced my connection code with Bengal's suggested "goSQL" function. Same result - connection is fine, but I'm still getting the error that the recordset is closed even when I explicitly open it.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try adding:
SET NOCOUNT ON
as the first command in your SQL statement.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Does it make any difference if you omit dropping the table at the end? Are you still testing EOF as suggested previously?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,518
Messages
5,636,807
Members
416,941
Latest member
shazzaxyz

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