Recordset from SQL Stored Procedure not opening

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

Who is Participating?
mizveggieAuthor Commented:
Could this be related to the versions of software I am running?  I am running Excel 2007 and SQL Server 2000.
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.


mizveggieAuthor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.