Excel 2010/2013 VBA accessing SQL 2012 Stored Procedure Error 3265

I have the following code in a spreadsheet:

Dim strConn As String
Dim conn As New Connection
Dim cmd As New ADODB.Command
Dim rs As New Recordset
strConn = "DRIVER=SQL Server;SERVER=AV-W12-ROMS-1;DATABASE=RESUMES"

conn.ConnectionString = strConn
conn.Open
cmd.ActiveConnection = conn
cmd.CommandTimeout = 0
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_list_updated_documents_within_x_days"
cmd.Parameters("@number_of_days") = PromptNoDays.TextBoxDays.Text

Set rs = cmd.Execute
        
rs.MoveFirst
...

Open in new window

and here is the beginning of the Stored Procedure:
USE [RESUMES]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_list_updated_documents_within_x_days]  
	-- Add the parameters for the stored procedure here
	@number_of_days nvarchar(1024) 
AS
BEGIN
...

Open in new window

I can run this without a problem on my computer but it we get the "Item cannot be found in the collection corresponding to the requested name or ordinal" error when it runs on another user's computer when it hits this line in the VBA Code:
cmd.Parameters("@number_of_days") = PromptNoDays.TextBoxDays.Text

Open in new window

I'm using Excel 2013, the other user 2010.  Any ideas on why it works for me but not on another computer?
wchestnutAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nutschConnect With a Mentor Commented:
Damn cut-and-paste-and-tweak, it should be

        cmd.Parameters.Append cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

My bad,

Thomas
0
 
nutschCommented:
Can you try:

        cmd.Parameters.Append prm cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

Thomas
0
 
wchestnutAuthor Commented:
cmd.Parameters.Append prm cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

Open in new window

With that I get a Syntax Error?
0
 
wchestnutAuthor Commented:
No, you good! : )  It worked!

Strange that the original line worked on mine but not the user's.  I searched all over the place for the error and proper command line and thought I had the correct one.  I do have Visual Studio Express 2012 for Windows Desktops, the SQL Server Management Studio plus some web development tools installed on mine so maybe they included something that allowed it to work.

Thanks, Thomas!
0
 
DataSenseConsultant/Data AnalystCommented:
I have a similar problem with a call to a sql server (2014) stored procedure which works when called from Access 2013 but not from Access 2007.

This is the stored proc:

CREATE PROCEDURE uspGetStockOnOrder
@OnOrder int output,
@StockID int
AS
      set nocount on;
      SELECT @OnOrder = dbo.vStock_on_order.OnOrder
      FROM dbo.vStock_on_order
      WHERE dbo.vStock_on_order.Ord_SppSizeID = @StockID;
      RETURN
Go
---------
In VBA the code is:
Public Function GetStockOnOrder(Param1 As Long, Param2 As Long) As Long
On Error GoTo ConnectError
Dim objConnection As New ADODB.Connection, objCom As ADODB.Command, strConnect As String, DataSrce As String, RetVal As Integer
    DataSrce = DLookup("ServerName", "Master_Central", "User_Loc = '" & Pub_UserLoc & "'")
'      different users can have different paths to the server
    Set objCom = New ADODB.Command
    objConnection.Provider = "sqloledb"
    strConnect = "Integrated Security=SSPI; Persistent Security info = false; Data Source = " & DataSrce & "; Initial Catalog=CM_BE"
    objConnection.Open strConnect
    With objCom
        .ActiveConnection = objConnection
        .CommandType = adCmdStoredProc
        .CommandText = "uspGetStockOnOrder"
        .CreateParameter ("@OnOrder")
        .CreateParameter ("@StockID")
        .Parameters("@StockID").Value = Param1
        .Parameters("@OnOrder").Value = Param2
        .Execute
        GetStockOnOrder = Nz(CLng(.Parameters("@OnOrder").Value))    ' return value
    End With

Exit_ConnectError:
    Exit Function
ConnectError:
GetStockOnOrder = -1    ' flag to convey error to calling routine
    errornum = Err.Number
    If errornum = -2147467259 Then
        MsgBox "The back-end database name or path to the server is not valid." & vbCrLf & "      DO NOT CONTINUE!", vbCritical, "DataSense Help"
        DoCmd.Close acForm, Pub_FormName
        Resume Exit_ConnectError
    End If
End Function
--------------------
As mentioned above, this works OK from computers running the latest version of Access, but not on those running Access 2007.
As suggested in the above answer, I have tried using a more complete definition of the parameters in the calling routine:
   .Parameters.Append   .CreateParameter("@StockID", adInteger, adParamInput, 4)
   .Parameters("@StockID").Value = Param1
 etc
But this still bombs out when the .Execute statement is reached.

I would be grateful if someone could point out what I'm doing wrong (or not doing right).
0
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.