how to pass values to a calling stored procedure

[krishna1412]

Currently i am working in a project of report generation in MS ACCESS.

The tables are in sql server 2000.
I have to write stored proc in ms access.

Illustration:
I am having a stored proc as follows

name: myproc
-------------------
Create procedure my_proc
@f1 char(1),
@f2 char(5)
As
select * from table1 where field1=@f1 and field2=@f2
_______________ _______________ _______________ ___
and calling proc
name: call_myproc

execute my_proc 'A','2004'

It works fine. I have got all the values which satisfies the above condition in report.

If i am getting the vales of field1 and field2 from forms (form_a) in ms access.
I have to write the calling proc as follows

my_proc [forms]![form_a].[Combo4],[forms]![form_a].[text12]

But ms access throws syntax error.
How is it possible to pass values from ms access FORMS to a calling stored procedure.

could you please help me to fix this problem ?

[MMcCarthy]

Access requires single quotes surrounding string values passed this way.

my_proc "'" & [forms]![form_a].[Combo4] & "'", "'" & [forms]![form_a].[text12] & "'"

Currently i am working in a project of report generation in MS ACCESS.

The tables are in sql server 2000.
I have to write stored proc in ms access.

Illustration:
I am having a stored proc as follows

name: myproc
-------------------
Create procedure my_proc
@f1 char(1),
@f2 char(5)
As
select * from table1 where field1=@f1 and field2=@f2
_______________ _______________ _______________ ___
and calling proc
name: call_myproc

execute my_proc 'A','2004'

It works fine. I have got all the values which satisfies the above condition in report.

If i am getting the vales of field1 and field2 from forms (form_a) in ms access.
I have to write the calling proc as follows

my_proc [forms]![form_a].[Combo4],[forms]![form_a].[text12]

But ms access throws syntax error.
How is it possible to pass values from ms access FORMS to a calling stored procedure.

could you please help me to fix this problem ?

[PEB]

Yeap,
Interesting, nevetr knew that in Access project you can use the stored procedures in SQL Server... Never tried!

But sounds like Access is done for interface of SQL Server :)

I've read somhere that Access is the second one application used for user interface with Oracle After the Oracle offered interface!

What do you think about???

:)

[MMcCarthy]

Yeap,
Interesting, nevetr knew that in Access project you can use the stored procedures in SQL Server... Never tried!

But sounds like Access is done for interface of SQL Server :)

I've read somhere that Access is the second one application used for user interface with Oracle After the Oracle offered interface!

What do you think about???

:)

Access is ideal as a GUI for sql databases because to a great extent it is designed to be used that way.

The thing to remember when passing stored procedures is to use the syntax of the backend server for all sql rather than Access sql.

You can also create what are known as pass thru sql queries in access once you have an odbc connection to the server. These are sql queries written in the sql syntax of the applications backend and are executed directly on the backend rather than relying on frontend links.

The stored procedure feature simply requires you to create a connection in vba to the backend and .Execute the procedure as in the aforementioned example.

I'm sure some of our other experts will have a lot more knowledge on the subject.

Mary

[PEB]

Thanks Mary for the info,

Maybe here is important to not confuse the SQL standarts... Coz SQL Server uses SQL92 till Access ordinary uses SQL89

But using a VB procedures should be run locally from Access and the Query should be written on SQL 89 isn't it?

[NeoPa]

You can access SQL Server tables from Access straightforward ly if they are linked.
To access (use) native SQL Server facilities like SPs (Stored Procedures) and UDFs (User Defined Functions) you need to program Pass-Through queries.
Pass-Through queries are written in the native language of the system connected to (in other words, for MS SQL Server Transact-SQL).
Pass-Through queries run as if they were on the SQL Server itself - hence can access SPs & UDFs without a problem.

[PEB]

Thanks Adrian,

So if the query isn't Pass trou, so it is executed locally by the Access Jet database engine. Ok now clear!

Thanks a lot my friends :)

[NeoPa]

That's absolutely right PEB - You've got it.

[krishna1412]

Thank you very much for your response.

I am creating and executing the stored procedure in the same way as given in the following link.

http://www.databasejournal.com/features/msaccess/article.php/10895_3363511_3

I have tried by giving like this.

EXECUTE call_RSM_Query "'"&[forms]![rsm_form].[Combo0]&"'","'"&[forms]![rsm_form].[Combo6]&"'","'"&[forms]![rsm_form].[Combo8]&"'","'"&[forms]![rsm_form].[text62]&"'","'"&[forms]![rsm_form].[text63]&"'","'"&[forms]![rsm_form].[text57]&"'","'"&[forms]![rsm_form].[text58]&"'", "'"&[forms]![rsm_form].[text59]&"'","'"&[forms]![rsm_form].[text60]&"'"

It throws the following error.

PLA daily report
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrect syntax near '&' #170

I think the problem is with the syntax. What is the correct syntax to pass values from forms to a calling stored proc.

[NeoPa]

Check out the Help for Execute to see how it should be used.

Execute Method


Runs an action query or executes an SQL statement on a specified Connection or Database object.

Syntax

object.Execute source, options

querydef.Execut e options

The Execute method syntax has these parts.

Part Description
object A Connection or Database object variable on which the query will run.
querydef An object variable that represents the QueryDef object whose SQL property setting specifies the SQL statement to execute.
source A String that is an SQL statement or the Name property value of a QueryDef object.
options Optional. A constant or combination of constants that determines the data integrity characteristics of the query, as specified in Settings.

[MMcCarthy]

Thank you very much for your response.

I am creating and executing the stored procedure in the same way as given in the following link.

http://www.databasejournal.com/features/msaccess/article.php/10895_3363511_3

Ok you are trying to design the Execute in the Pass thru sql query window. I thought you were using vba.

Can you give an example of what the query would look like if you used values instead of the combo box.

[krishna1412]

When I give like this. The calling proc executes fine. I got the output.
execute my_proc 'A','2004'.
_______________ _______________ _______________ _____________
example.
-------------
let me explain elaborately.
-------------------------------------
I have created a form named [Form3] consists of combo box "Department " values (eee,ece,mech,c se). and I created a table in SQL Server named register consists of the fields stud_name, register_num,ma rk1,mark2,mark3 . And I am having a query query1. "select * from dbo.register where department=[forms]![form3].[combo3]. And I am having a report Report1, will display Name, mark1, mark2, mark3.

When the user chooses the department as "ece".
The report1 will be generated consists of the all the "ece" students names and their marks.
It works fine.

Now i need to do it with stored procedure.

I wrote a stored procedure as follows.
name of the pass through query: my_proc
-------------------------------
create procedure str_proc
@dept
as
select stud_name,mark1 ,mark2,mark3 from dbo.register where department=@dep t
-----------------------------
and executed this pass through query. I did not get any errors.

I wrote another-pass through query as follows.

execute str_proc 'ece'

I got all the values (name and marks)

I gave it like

execute str_proc [form]![form3].[ combo3]

It throws error message.
Then i have given the following code in the report1 - on open event.[Event Procedure]
------------------------
Dim cnn as ADODB.Connectio n
Dim sqlstr as String

sqlstr = "execute str_proc (" & [forms]![form3]![combo3 & ");"
set cnn = new ADODB.Connectio n
with cnn
.ConnectionStri ng = globalDS
.Open
.Execute(sqlstr )
.Close
end with

set cnn = nothing
--------------------------
But still values of name,mark1,mark 2, mark3 not populating in the text boxes on the report1. what is the solution for this problem.

could you please help me to fix this problem.

[MMcCarthy]

I gave it like

execute str_proc [form]![form3].[ combo3]

It throws error message.

You can't do this directly in the pass thru window as [form]![form3].[ combo3] means nothing outside of Access and you are trying to execute directly on the server

You could try something like this ...

   
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command

   conn.ConnectionString = globalDS
   conn.Open

   cmd.CommandText = "str_proc '" & [Forms]![form3]![combo3] & "'"
   cmd.CommandType = adCmdStoredProc
   cmd.ActiveConnection = conn
   cmd.Execute
 
   conn.Close

[krishna1412]

But the values are not populating in the report.
eg.
I am having report like this

stud_name Mark1 Mark2 Mark3
_________ _____ ____ ____
| | | | | | | | ----> text boxes.
----------------- ---------- -------- --------

but the values are not populating in the corresponding textboxes.

what should i have to do?
I have given ur coding in report on open event !! is that correct ?
or i have to give ur coding in the form button event on click.

also I havenot given anything in the record source of report properties.

how to proceed furthur.

[NeoPa]

If you are trying to run a report, then I can't see how executing the SP remotely will help at all.
Execute runs an action query. Even if it returns results, how do you get the report to use them?
You need to design a Pass-through query in access which references the form control.
This will be like a template but will be changed every time you run it. Set the Tag to be the template SQL with a marker that needs changing for every instance. When you want to run it, update the SQL by getting the Tag SQL data and updating the marker with the current value required (in your VBA code). Save this in the SQL property of the query.
Set the reports Data Source property to this query and all should be well.
NB Formatting is never performed on the Pass-through query results so if you want to test the data beforehand, bear this in mind.