in access calling a stored procedure in sql server

Hi all,

I have a report that I want to run from a form.

Background:

I created a stored procedure in SQL Server and it has two parameter values: Month (int) and year (int). I created a pass through query in Acess that executes the stored proc with the two parameter values I supplied:


EXECUTE spM_bodies_and_units_detail_rpt 10, 2005


This works and the report works. Now the issue I have is that I want the pass through query to take two parameter values from two fields on the form:

[forms]![frmReportMenu]![txtMonth]
[forms]![frmReportMenu]![txtYear]

I have tried the following in the pass through query:

EXECUTE spM_bodies_and_units_detail_rpt [forms]![frmReportMenu]![txtMonth], [forms]![frmReportMenu]![txtYear]

this gives me an error:

ODBC--call failed
Incorrect syntax near '!'. (#170).

Has anyone tried this? I was hoping someone might have an idea. Thanks for any help on this.

I'd use some vba-code to call stored procedures on a postgres server.
Basicallay something linke:
(GlobalDS might look like "DSN=my_dsn;" and refer to a user or system DSN setup by the ODBC Control )

Dim cnn as ADODB.Connection
Dim sqlstr as String

sqlstr = "execute spM_bodies_and_units_detail_rpt (" & [forms]![frmReportMenu]![txtMonth] & ", " & [forms]![frmReportMenu]![txtYear] & ");"
set cnn = new ADODB.Connection
with cnn
.ConnectionString = globalDS
.Open
.Execute(sqlstr)
.Close
end with

set cnn = nothing

-------------
maybe you must double-double quote your procedure name - it would need to be done with postgres, when you use names in mixed cASes...

An example of how I call stored procs from vba is in the attachement of this posting: http://forums.databasejournal.com/sh...ad.php?t=40887

I was able to get it working through vba, though I did it a little different.

I did some researching on why the pass thru query wasn't working as I thought it should, and basically I found that the pass through query works like the sql server's Query Anlalyzer, where it will be 'read' on the 'server' side' - so sql server would have no idea what a [Form]![field name] is since that is strictly an access object.

I decided to go the Querydef route as I have learned from past experiences that you can create and modify an existing query through the querydef. I figured if I could simply change the query parameters through VBA. Below is the 5 lines of code I used to change the querydef before it opens the report - it works great

(fyi: I did have to add the reference for DAO as I was using Access 2003 which uses ADO. ADO doesnt recognise the querydef but DAO does).

Dim qdfList As QueryDef
Dim strSQL As String

Set db = CurrentDb

Set qdfList = db.QueryDefs("BODIES_AND_UNITS_MAM") 'passthrough query
strSQL = "EXECUTE spM_bodies_and_units_detail_rpt " & [Forms]![frmReportMenu]![txtMonth] & ", " & [Forms]![frmReportMenu]![txtYear] 'passing in variables
qdfList.SQL = strSQL

stDocName = "REPORT NAME"
DoCmd.OpenReport stDocName, acPreview

I am new to VB. Currently i am working in ms access.
explanation.
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.
------------------------
Dim cnn as ADODB.Connection
Dim sqlstr as String

sqlstr = "execute spM_bodies_and_units_detail_rpt (" & [forms]![frmReportMenu]![txtMonth] & ", " & [forms]![frmReportMenu]![txtYear] & ");"
set cnn = new ADODB.Connection
with cnn
.ConnectionString = globalDS
.Open
.Execute(sqlstr)
.Close
end with

set cnn = nothing
--------------------------
in the report1 - on open event. But still values 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.