call stored procedure from ms access 97

[leangross]

Can anyone help me how can i call the the stored procedure i made in SQL in my ms access 97 program.

I have 4 parameter in SQL stored procedure.

Hope you could me samples. Thanks in advance!!!

 

[boblovesyousomuch]

Hi,

You can do this:

Dim qdf As QueryDef           
Set qdf = CurrentDb.QueryDefs("NameOfQuery")
qdf.Parameters![@param1] = value1
qdf.Parameters![@param2] = value2
qdf.Parameters![@param3] = value3
qdf.Parameters![@param4] = value4
qdf.Execute

The @param1 etc is what you parameter is called in your query.

Hope that helps?!

Carl

 

[JeffTullin]

I need to do something similar to the original post, and that looks neat, BUT the problem for me, and I suspect for leangross, is that the query is not part of CurrentDb.

Its actually sitting across an ODBC connection in a SQL Server data base.
You don't happen to know how to set up a querydef for a remote db?

 

[leangross]

Thanks for the replies guys!!!
The stored procedure is in SQL server not from MS Access.
Anyone knows how?

Thanks in advance!!!

 

[1manedp]

You can use an SQL pass-through query. This allows you to write the SQL code exactly as you want to see it.

exec MyMSSQLStoredProcedure 'parm1','parm2', parm3

You can use vba to modify the properties of the query -- even change to procedure you run.

Make sure to specify whether or not you want the pass-through query to return data.

Hth!

 

[cmmrfrds]

If the stored procedure returns a recordset and you want it bound to a Form them it must be (Access 97) a pass-thru query as suggested by 1manedp. If you want to return a recordset to be worked on in vba code, then the ADO Command Object (will work in Access 97) can be used.