By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

how to pass values to a calling stored procedure

P: 4
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 ?
Nov 17 '06 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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 ?
Nov 17 '06 #2

PEB
Expert 100+
P: 1,418
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???

:)
Nov 18 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #4

PEB
Expert 100+
P: 1,418
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?
Nov 18 '06 #5

NeoPa
Expert Mod 15k+
P: 31,616
You can access SQL Server tables from Access straightforwardly 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.
Nov 18 '06 #6

PEB
Expert 100+
P: 1,418
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 :)
Nov 18 '06 #7

NeoPa
Expert Mod 15k+
P: 31,616
That's absolutely right PEB - You've got it.
Nov 18 '06 #8

P: 4
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/featu...0895_3363511_3

I have tried by giving like this.
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 21 '06 #9

NeoPa
Expert Mod 15k+
P: 31,616
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.Execute 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.
Nov 21 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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/featu...0895_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.
Nov 21 '06 #11

P: 4
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,cse). and I created a table in SQL Server named register consists of the fields stud_name, register_num,mark1,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=@dept
-----------------------------
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.Connection
Dim sqlstr as String

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

set cnn = nothing
--------------------------
But still values of name,mark1,mark2, 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.
Nov 25 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim conn As New ADODB.Connection
  3. Dim cmd As New ADODB.Command
  4.  
  5.    conn.ConnectionString = globalDS
  6.    conn.Open
  7.  
  8.    cmd.CommandText = "str_proc '" & [Forms]![form3]![combo3] & "'"
  9.    cmd.CommandType = adCmdStoredProc
  10.    cmd.ActiveConnection = conn
  11.    cmd.Execute
  12.  
  13.    conn.Close
  14.  
  15.  
Nov 25 '06 #13

P: 4
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.
Nov 26 '06 #14

NeoPa
Expert Mod 15k+
P: 31,616
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.
Nov 26 '06 #15

Post your reply

Sign in to post your reply or Sign up for a free account.