Solved

Executing Stored Procedure from Access

Posted on 2008-06-11
4
1,087 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:slamhound
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21766767
Trying adding the the owner prefix to the sp name. (dbo.usp_printerorder_pending or whatever it is)
0
 
LVL 1

Expert Comment

by:treanor99
ID: 21766937
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
 
LVL 10

Author Comment

by:slamhound
ID: 21774527
Referencing the owner and server name etc has no effect. I'm not setup using a project.
0
 
LVL 10

Accepted Solution

by:
slamhound earned 0 total points
ID: 21808181
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now