Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 30th, 2004, 01:38 PM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored procedure and VBA

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
 
Old July 30th, 2004, 03:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old July 30th, 2004, 03:43 PM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 30th, 2004, 04:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

:D Grinnin' back. My pleasure Jesse.

Bob

 
Old August 2nd, 2004, 07:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old August 3rd, 2004, 09:26 AM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 7th, 2007, 10:51 PM
Registered User
 
Join Date: Apr 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to aballeras
Default

thanks guys for this info. been a great help.


 
Old September 15th, 2011, 12:46 PM
sh7 sh7 is offline
Registered User
 
Join Date: Sep 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Get Stored Procedure Output using Access VBA LandLocked Access VBA 3 June 20th, 2007 11:20 AM
Execute Stored Procedure from VBA Romashka Access VBA 1 February 22nd, 2007 12:52 PM
Running stored procedure in Access VBA Bryon Burbage Access VBA 0 July 21st, 2006 09:14 AM
MS SQL Stored procedure and VBA jesseleon Pro VB Databases 0 July 30th, 2004 01:16 PM
Create Stored Procedure in VBA Mitch Access 0 April 14th, 2004 03:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.