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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
I would suggest checking if there is data in the recordset as soon as you have created it:

Code:
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

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

Banned user
Joined
Oct 19, 2012
Messages
141
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

New Member
Joined
Jan 7, 2013
Messages
25

ADVERTISEMENT

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

Banned user
Joined
Oct 19, 2012
Messages
141
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:
Rich (BB code):
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

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

ADVERTISEMENT

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.
 
Last edited:

Viceroy369

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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

Banned user
Joined
Oct 19, 2012
Messages
141
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,512
Messages
5,636,764
Members
416,939
Latest member
Rajakumaran

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