We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Recordset from SQL Stored Procedure not opening

Medium Priority
Last Modified: 2012-05-06
I have been trying for days now to open a recordet in excel 2007 from a stored procedure in SQL Server.  The error I receive is "operation not allow on closed object."  So I put some messagin in my code to determine if the connection or the recordset was the problem.  The connection state is open, however the recordset state is closed.  When I execute the code, it actually pauses on the open recordset as if it is retreiving the data, but the next line of code tests its state and it is always closed.  I validated permissions on the SP, and that should not be a problem.  Please look at this code and help me figure out what could possibly be wrong or what I need to test.  Thanks!
Sub GetBom_Click()
Dim strPart As String
Dim cn As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim strConn  As String
Dim qt As QueryTable
Set cn = New ADODB.Connection
strPart = Worksheets("M2MBOM").Range("B1")
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=MyServer; INITIAL CATALOG=Database01; USER ID=test; Password=test"
'This procedure requires one parameter (i also tested with another proc requiring no variables, does not work
strSQL = "Proc_Get_Item strPart"
cn.Open strConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'Here the connection state is 1
MsgBox cn.State
'Here the rs state is 0
MsgBox rs.State
If rs.State = adStateOpen Then
If rs.BOF And rs.EOF Then
    MsgBox "This Part Number does not have a Bill of Materials."
    Exit Sub
End If
ActiveSheet.Range("A2").CopyFromRecordset rs
Set rs = Nothing
MsgBox "Unable to open recordset.  Please notify adminsitrator."
End If
Set cn = Nothing
End Sub

Open in new window

Watch Question


Hello mizveggie,

Hi try using the connection execute method instead of the recordset open method. Or also set the CommandType property of the recordset before using your method.


Let me know.




I changed by code to include the stored procedure type  - this works where I have no parameter.  It still looks like its executing, but the recordset does not remain open.

When I try to execute with a parameter, I get a syntax error.  The reference you referred me to is good, but does not have the sample of code I am looking for (includes parameter syntax).

cn.Open strConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
Set rs = cn.Execute("MP_Get_BOM1", , adCmdStoredProc)
MsgBox cn.State
MsgBox rs.State

Open in new window

Could this be related to the versions of software I am running?  I am running Excel 2007 and SQL Server 2000.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.