Running a Stored Procedure from ADO

Hi,
From one of my forms in an Access Project I want to run a Stored Procedure on the "After Insert" event of the form. The stored procedure is an Insert SQL Statement with two variables.
What is the best way to pass the variables to the stored procedure and run it.

Should I run a SQL Statement like

srtSQL="EXEC SP_Insert (@.Var1=Var1,@.Var2=Var2)

Or there is a better way to do it with ADO objects?

Thanks
==============================================

I found two solutions. Which one do you think is the better one?
The stored procedure on the Server:

CREATE PROCEDURE SP_Insert_Into_CompanyAdrsContact
@.CompAdrsID int,
@.ContactID int
AS
Insert into tblCompanyAdrsContact (CompAdrsID,ContactID) Values (@.CompAdrsID,@.ContactID)
GO

On the Access form:

1) Using a SQL stament dynamically to pass parameters from a form:

strSQL = "Exec SP_Insert_Into_CompanyAdrsContact " & Me!CompAdrsID
strSQL = strSQL & "," & Me!ContactID
DoCmd.RunSQL (strSQL)

**********************************************
2) Using ADO objects:
Dim cmd As ADODB.Command
Dim prmContactID As ADODB.Parameter
Dim prmCompAdrsID As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_Insert_Into_CompanyAdrsContact"

Set prmCompAdrsID = cmd.CreateParameter("@.CompAdrsID", adInteger, adParamInput)
Set prmContactID = cmd.CreateParameter("@.ContactID", adInteger, adParamInput)

cmd.Parameters.Append prmCompAdrsID
prmCompAdrsID.Value = Me![CompAdrsID]
cmd.Parameters.Append prmContactID
prmContactID.Value = Me![ContactID]

cmd.ExecuteIf you are going to call this proc many times, it's best to use command parameter. This will allow you to gain some performance because of cache. If it's just a one time thing, just execute the string is fine.

RUN SQL Stored Procedure in Access

Hi there
I have a SQL stored procedure which I would like to run through Access.
This procedure has a paramert.
I created a form and by clicking a buttom I want to run this procedure but before to be able to input the parametr as well.

I am not sure if there is any way I can do this in the Access interface or wherther I have to use VBA code(which I am not familiar with)

Thank you very much.
Regards.
Pete

Quote:

Originally Posted by petr_podskubka

Hi there
I have a SQL stored procedure which I would like to run through Access.
This procedure has a paramert.
I created a form and by clicking a buttom I want to run this procedure but before to be able to input the parametr as well.

I am not sure if there is any way I can do this in the Access interface or wherther I have to use VBA code(which I am not familiar with)

Thank you very much.
Regards.
Pete

I would like to know if you would have information I trying to find information on how can I have my SQL stored procedure through Access...

Thank you for helping

|||

Quote:

Originally Posted by petr_podskubka

Hi there
I have a SQL stored procedure which I would like to run through Access.
This procedure has a paramert.
I created a form and by clicking a buttom I want to run this procedure but before to be able to input the parametr as well.

I am not sure if there is any way I can do this in the Access interface or wherther I have to use VBA code(which I am not familiar with)

Thank you very much.
Regards.
Pete

it will be a little bit of both...you need the VBA to establish connection and some setting. although there are objects that can handle some of these tasks, you still might need some VBA coding to handle some stuff, ie, error handling.

|||Try this:

http://archives.postgresql.org/pgsq...05/msg01260.php

Basically what it does, it changes text of your query every time you pass parameters.
The best part it is ready to use you dont need to write it yourself.

Good Luck.

Irina.|||

Quote:

Originally Posted by petr_podskubka

Hi there
I have a SQL stored procedure which I would like to run through Access.
This procedure has a paramert.
I created a form and by clicking a buttom I want to run this procedure but before to be able to input the parametr as well.

I am not sure if there is any way I can do this in the Access interface or wherther I have to use VBA code(which I am not familiar with)

Thank you very much.
Regards.
Pete

Look at ADP files in Access stored procedures are exposed in the GUI interface along with views you enter paramters for the stored procedure via the Forms Inputparameters property of the form

Regards

Jim

|||

Quote:

Originally Posted by Jim Doherty

Look at ADP files in Access stored procedures are exposed in the GUI interface along with views you enter paramters for the stored procedure via the Forms Inputparameters property of the form

Regards

Jim

As I understood this question it is a pass through query that runs on SQL Server side and not on Access side.

|||

Quote:

Originally Posted by iburyak

As I understood this question it is a pass through query that runs on SQL Server side and not on Access side.

Hi Iburyak,

You're probably right of course and me way off the mark at the end of the day :)... but the poster simply mentioned 'Access' and my presumption is that they might not be aware that in Access .ADP files as distinct from mdb files were intended to work directly with SQL Server. The connection method is (universal data link) automated within the interface they might want to consider their options?

Regards

Jim

Run Dynamic Query Using Stored Procedure

Hi,

I need to create a stored procedure, which needs to accept the column name and table name as input parameter,

and form the select query at the run time with the given column name and table name..

my procedure is,

CREATE PROC spTest

@.myColumn varchar(100) ,

@.myTable varchar(100)

AS

SELECT @.myColumn FROM @.myTable

GO

This one showing me the error,

stating that myTable is not declared..

..........as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters..

Plese help me?? Is it possible in stored procedure..

DECLARE @.sql as Char(500) -- or whatever length is needed
SELECT @.sql = 'SELECT ' + @.myColumn + ' FROM ' + @.myTable
EXEC(@.sql)