calling stored procedure from access query

[Sreeneet]

Hi all,

I want to call a stored procedure which is written in SQL Server from
an ms-access query. It is having some parameters also and the stored
procedure will return some records.

Is there any way to do this?

Any help?

Thank you

[MGFoster]

Sreeneet wrote:

Hi all,

I want to call a stored procedure which is written in SQL Server from
an ms-access query. It is having some parameters also and the stored
procedure will return some records.

Is there any way to do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here' is how I do it, using DAO.

0. The SP that returns records does it w/ a SELECT statement NOT a
RETURN statement.
1. Have a QueryDef already set up as a SQL pass-thru query.
2. VBA code like this:

dim db as dao.database
dim qd as dao.querydef
dim rs as dao.recordset

set db = currentdb
set qd = db.querydefs("query_name")
qd.SQL = "usp_MyProcedure 'param1', param2, param3"

' If in a Report_Open() event you can set the report's RecordSource to
' the query: Me.RecordSource = "query_name" and the report will run
' the stored procedure.

set rs = qd.openrecordset()
' ... do what you want w/ the recordset ...

You can concatenate other values in the "param" values.

You can also do this in ADO, which has a "ReturnValue" parameter that
will pick up the RETURN <value> from the stored procedure. See the ADO
help articles on CreateParameter and Command Object for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbjnz4echKqOuFEgEQJdLACcDv+OyKsTjFujJ7HVnlV6u3 R5k1cAniue
JWfXTQGQjQ/8618/vemCq1NB
=s8W7
-----END PGP SIGNATURE-----