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!

 

[xenou]

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?

 

[BengalMagic]

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]

BengalMagic, it is the *recordset* object that is closed and will not allow the operation. Not the connection object.
ξ

 

[BengalMagic]

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]

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:

cn.Open
MsgBox cn.State

An open connection should return 1

 

[Viceroy369]

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:

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]

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]

Try adding:
SET NOCOUNT ON
as the first command in your SQL statement.

 

[Viceroy369]

Try adding:
SET NOCOUNT ON
as the first command in your SQL statement.

The code now runs without errors, but doesn't return any rows.

 

[RoryA]

Does it make any difference if you omit dropping the table at the end? Are you still testing EOF as suggested previously?