slamhound
asked on
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?
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'
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
Trying adding the the owner prefix to the sp name. (dbo.usp_printerorder_pend ing or whatever it is)
Is your database set up as project, i.e. the name ends .adp?
If so this code should work
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
ASKER
Referencing the owner and server name etc has no effect. I'm not setup using a project.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.