MS Access FE SQL Server BE Create Stored Procedure ERROR

I am getting a runtime error 3146  SQL Server proc expects parameter @product_nbr which was not supplied.  

The product_nbr originates from txtProduct_Nbr on a form in Access.

How can I supply this parameter?
deer777Asked:
Who is Participating?
 
Dale FyeCommented:
I do is something like:

dim qdf as dao.querydef
set qdf = currentdb.querydefs("yourQueryName")
qdf.SQL = "Exec spname " & me.txtProduct_nbr
qdf.Execute

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
How are u calling the sp. It should be like below..

Exec spname 'productname'
0
 
Fabrice LambertFabrice LambertCommented:
A more elaborated and more versatile function can look like:
Public Sub execStoredProcedure(ByVal strProcName As String, ParamArray parameters() As Variant)
On Error GoTo Error
    Dim oConn As Object    '// ADODB.Connection
    Dim oCmd As Object     '//ADODB.Command
    Dim strSQL As String
    Dim i As Integer
    
    
    strSQL = vbNullString
    strSQL = strSQL & "EXECUTE " & strProcName
    For i = LBound(parameters) To UBound(parameters)
        If (VarType(parameters(i) = vbString)) Then
            strSQL = strSQL & " '" & parameters(i) & "'"
        Else
            strSQL = strSQL & " " & parameters(i)
        End If
        If (i < UBound(parameters)) Then
            strSQL = strSQL & ","
        End If
    Next
    Set oConn = createobject("ADODB.Connection")
    oConn.ConnectionString = '// your connection string here
    oConn.Open
    Set oCmd = createobject("ADODB.Command")
    oCmd.CommandText = strSQL
    Set oCmd.ActiveConnection = oConn
    oCmd.CommandTimeout = 60
    Set oCmd = Nothing
    oConn.Close
    Set oConn = Nothing
Exit Sub
Resume
Error:
    If Not (oCmd Is Nothing) Then
        Set oCmd = Nothing
    End If
    If Not (oConn Is Nothing) Then
        If (oConn.State = adStateOpen) Then
            oConn.Close
        End If
        Set oConn = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Sub

Open in new window


Let's say you have stored procedure name setTime that take hours, minuts and seconds as parameters, you would call the function like this:
execStoredProcedure "setTime", 12, 30, 45

Open in new window

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.