I have an ADP (Access Project) as a front end for MS SQL. In the Db I have a stored procedure that requires one parameter and then builds some temp tables and updates the Db.

My problem is that I can run it from SQL's query analyser or by clicking on it in Access and manually entering the parameter and it runs perfectly but when I try to execute it from with in a button's click event it states that one of the temporary tables can not be found.

I looked into DOCMD.openstoredprocedure but it does not have a way of sending parameters.

The code that I'm using in the click event is as follows;
Dim strMyID as integer
strMyID = Me.SS_ID.Value 'Field from the Access form
DOCMD.RUNSQL "Exec spUpdate " & strMyID
HELP!:)



Thanks,
Jesse

__________________
Thanks,
Jesse

Hi Jesse,

One way would be to use an ADO Command object and have your stored procedure evaluated as a text sting:

' In form module
Private Sub Command1_Click()
    Dim cmd As ADODB.Command
    Dim intMyID As Integer

    intMyID = 1

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "EXEC procADPParameters " & intMyID
        .CommandType = adCmdText
        .Execute
    End With

End Sub

My stored procedure simply looks like this:

CREATE PROCEDURE procADPParameters
    @MyID int
AS
    create table test (myField int)
    insert into test(myField)values(@MyID)

HTH,

Bob

Bob, I have a grin from ear to ear! :D

It works perfectly. Excellent example!!!

Thank you very much for sharing your time and knowledge.

Jesse

Thanks,
Jesse

:D Grinnin' back. My pleasure Jesse.

Bob

Hi Jesse,

Just as a follow up, I bumped into this last night and had no idea it was possible. But its kinda' cool (found it in the MDAC 2.8 SDK). Has to be the most stream-lined way possible to execute a parameterized stored proc.

ADO lets you use the proc name to emulate a native method of the connection object. Just need 3 lines of code. Using the ‘procADPParameters’ procedure:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.procADPParameters intMyID

(Of course, Intellisense doesn't pick up the proc name because it doesn't know anything about it).

So the ADP Click event looks like:

Private Sub Command0_Click()
    Dim cnn As ADODB.Connection
    Dim intMyID As Integer

    intMyID = 1

    Set cnn = CurrentProject.Connection

    ' execute proc as a connection object "pseudo-method"
    cnn.procADPParameters intMyID

    cnn.Close
    Set cnn = Nothing

End Sub

This technique also allows you to return a recordset as an output parameter of a SELECT proc, even though the proc doesn't declare an output paramter. If I change the proc definition to:

CREATE PROCEDURE procADPParamSelect
    @MyID int
AS
    select * from test where myField = @MyID

..and place the following in the Click event:

Private Sub Command0_Click()

    Dim cnn As ADODB.Connection
    Dim rstOut As ADODB.Recordset
    Dim intMyID As Integer

    intMyID = 1

    Set cnn = CurrentProject.Connection

    Set rstOut = New ADODB.Recordset
    cnn.procADPParameters intMyID, rstOut ' recordset "pseudo-output paramter" variable

    ' Prints 1
    Debug.Print rstOut(0)

    rstOut.Close
    cnn.Close
    Set rstOut = Nothing
    Set cnn = Nothing

End Sub

… a forward-only, read-only, client-side recordset object is returned as an “output parameter”. (You can modify the recordset's cursor properties if you set them before executing the proc.)

Kinda’ cool.

Bob

That is really similar to the code that I'm using in the ASP.NET portion of this project. I never even thought of trying that route from Access. I guess MS is really rounding everything up in .NET

Thanks,
Jesse

thanks guys for this info. been a great help.

Hi,

have Access 2007 forms running on SQL server 2008. I am trying to get your code running but now sure about the sql query syntax that is passed to the database. Looking at the sql prfiler the execute command looks like:

execute sp_patient '111' ', ' ' xcc' ' ,....

Also If I want to return the result set to another form, how that can be done?


Private Sub Command0_Click()

Dim cnn As ADODB.Connection
Dim rstOut As ADODB.Recordset

Dim strPeriod As String
Dim strCountry As String
Dim strType As String

strPeriod = Me.cmb_period
strCountry = Me.cmb_country
strType = Me.cmb_Type
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "EXEC SP_Patient " & " '" & strPeriod & "', '" & strType & "','" & strCountry & "'" .CommandType = adCmdText
.Execute
End With
End Sub

Many thanks in advance