Solved

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

Posted on 2014-03-13
5
1,025 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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