Executing Stored Procedure from Access

I'm trying to run a stored procedure with parameters from Access.

I get an "object required" error message on the first parameter line.
If I remove the parameters, I get "The Microsoft Jet database engine cannot find the input table or query 'usp_printerorder_pending'. Make sure it exists and that its name is spelled corretly."

How do I set the parameters correctly and does it have something to do with not being able to find the stored procedure? And if so, how do I make it see the stored procedure?
Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = CurrentProject.Connection
    
    cmd.CommandText = "usp_printerorder_pending"
    cmd.CommandType = adCmdStoredProc
    
    'Set the parameters
    cmd.Parameters.Append (cmd.CreateParameter("@FromDate", adDate, adParamInput, 10, Me.dteFrom.Value))
    cmd.Parameters.Append (cmd.CreateParameter("@ToDate", adDate, adParamInput, 10, Me.dteTo.Value))
 
    Set rs = New ADODB.Recordset
    Set rs = cmd.Execute
    Me.frmPrinterOrdersub.Form.Recordset = rs
 
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing

Open in new window

LVL 10
slamhoundAsked:
Who is Participating?
 
slamhoundAuthor Commented:
I solved it myself.
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
 
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={SQL Server};Server=SOY-SQL;Database=SOYSourceSQL;UID=xxxx;Password=xxxxxx"
    cnn.CursorLocation = adUseClient
 
    Set cmd = New ADODB.Command 'New Command
 
    With cmd
        .ActiveConnection = cnn
        ' .ActiveConnection = Application.CurrentProject.Connection
        .CommandText = "usp_printerorder_pending"
        .CommandType = adCmdStoredProc
        .Prepared = True
        '.Parameters.Refresh
        .Parameters("@FromDate").Value = Me.dteFrom.Value
        .Parameters("@ToDate").Value = Me.dteTo.Value
        Set rs = .Execute
    End With
    
    Set Me.frmPrinterOrdersub.Form.Recordset = rs
 
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing

Open in new window

0
 
peter57rCommented:
Trying adding the the owner prefix to the sp name. (dbo.usp_printerorder_pending or whatever it is)
0
 
treanor99Commented:
Is your database set up as project, i.e. the name ends .adp?

If so this code should work
Function ExecSP(strSQL As String, Optional intTimeout As Integer = 360, Optional blnReturnError As Boolean = False) As String
' runs sql command
' creates log entry in command_log
' If there is an error than return it otherwise return ""
    Dim s_sql_log As String
    Dim objCmd As New ADODB.Command
 
    s_sql_log = _
        "INSERT INTO command_log " & _
        "(command, datetime_sent, user_sent) " & _
        "VALUES ('" & Replace(strSQL, "'", "''") & "', GETDATE(), '" & Environ("user") & "')"
 
    ' create log entry
    DoCmd.RunSQL s_sql_log
 
    ' run sql command with 3 minute timeout
    With objCmd
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdText
        .CommandText = strSQL
        .CommandTimeout = intTimeout
        If blnReturnError = True Then
            On Error Resume Next
            .Execute
            If Err.Number <> 0 Then
                ExecSP = Err.Description
            Else
                ExecSP = ""
            End If
            On Error GoTo 0
        Else
            .Execute
        End If
    End With
    Set objCmd = Nothing
End Function

Open in new window

0
 
slamhoundAuthor Commented:
Referencing the owner and server name etc has no effect. I'm not setup using a project.
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.