Solved

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

Posted on 2014-03-13
5
1,053 Views
Last Modified: 2016-02-11
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?
0
Comment
Question by:wchestnut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39927657
Can you try:

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

Thomas
0
 

Author Comment

by:wchestnut
ID: 39927670
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
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39927687
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
 

Author Closing Comment

by:wchestnut
ID: 39927779
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
 

Expert Comment

by:DataSense
ID: 40759520
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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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