Recordset from SQL Stored Procedure not opening

Posted on 2009-02-09
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

Question by:mizveggie
    LVL 22

    Expert Comment

    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.



    Author Comment

    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


    Accepted Solution

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now