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]

I would suggest checking if there is data in the recordset as soon as you have created it:

Set rs = objMyCmd.Execute
If Not rs.EOF then
    msgbox rs.fields(0).Value
Else
    msgbox "rs EOF!"
End If

Things look okay but it is different that you are dropping the temp table as part of your command text. Maybe that's a reason? Not sure - I've never done that before.

 

[Viceroy369]

Thanks for the response.

I tried the If statement you provided, but get the same error (operation not allowed when the object is closed).

 

[BengalMagic]

Thanks for the response.

I tried the If statement you provided, but get the same error (operation not allowed when the object is closed).

You need to F8 through your code, it seems to me that the cn.open command isn't doing what you want.

 

[Viceroy369]

You need to F8 through your code, it seems to me that the cn.open command isn't doing what you want.

I stepped through the code and there are no errors until the CopyfromRecordset line. Is there an easy way to test if the cn.open line isn't working properly?

Thanks!

 

[BengalMagic]

I stepped through the code and there are no errors until the CopyfromRecordset line. Is there an easy way to test if the cn.open line isn't working properly?

Thanks!

I'm not familiar with how you've set your whole protocol up, it seems overly complicated.

I use something like this:

Private gosql As Object
'
'first create a function that defines the SQL login credentials so that everything can be automated
'
Public Function Connect() As Boolean
Dim lsConnStr As String
If gosql Is Nothing Then
    lsConnStr = "Driver={SQL Server};" & _
                "Uid=" & gblcststrID & ";" & _
                "Pwd=" & gblcststrPWD & ";" & _
                "Server=" & gblcstrServer & ";" & _
                "Database=" & gblcststrLoadCatalog
'
'if any of the credentials are incorrect, jump to error trapping code
'
    On Error GoTo ConnectFailed
    Set gosql = CreateObject("ADODB.Connection")
    gosql.ConnectionTimeout = 5
    gosql.Open lsConnStr
End If
'
'connect successful
'
Connect = True
Exit Function
'
'error trap
'
ConnectFailed:
    MsgBox "Could not connect to server" & Chr(13) & err.Description
    Set gosql = Nothing
    Connect = False
End Function

The credentials are stored in a global module.

Never had a problem yet.

The highlighted line is what opens your connection for you.

 

[xenou]

I've been studying your sql and I think one problem is that you are using records in #temp that don't exist. When you create #temp you don't add any fields called plant1 or plant2 but when you query it you try to query on those fields. So that should create an error. Unless I am misunderstanding how the PIVOT works.

You could try doing this without using a temp table -- that's what I was attempting but I got lost with the [Plant1] and [Plant2] bit.

 

[Viceroy369]

I've been studying your sql and I think one problem is that you are using records in #temp that don't exist. When you create #temp you don't add any fields called plant1 or plant2 but when you query it you try to query on those fields. So that should create an error. Unless I am misunderstanding how the PIVOT works.

You could try doing this without using a temp table -- that's what I was attempting but I got lost with the [Plant1] and [Plant2] bit.

I believe the syntax is correct. This query runs correctly if I paste it directly into the SQL GUI. I originally tried doing the query with a subquery instead of a temptable but it errored out; simply moving the subquery to be a temptable fixed that issue.

 

[xenou]

Yeah, I figured that was what you'd say since it works in a query window. Not sure - if you don't mind providing a few rows of sample data I'll see if I can test too - will be easier if I know what kind of values are in these fields.

 

[BengalMagic]

I believe the syntax is correct. This query runs correctly if I paste it directly into the SQL GUI. I originally tried doing the query with a subquery instead of a temptable but it errored out; simply moving the subquery to be a temptable fixed that issue.

Your SQL is fine, it's your VBA code that's dodgy.