calling stored procedure from vba code

[hfs]

I have code for calling stored procedure with parameters,which is as follows

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long

IdValueToProcess = 221177  ' test data
    Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;Description=pending;DRIVER=SQL Server;SERVER=BELQA;UID=ha;Trusted_Connection=Yes;"
               
       
qdf.SQL = "EXEC dbo.[spItemDesc] " & IdValueToProcess
Set rst = qdf.OpenRecordset(dbOpenDynaset)
'objConnection.Open provStr

 just to make sure we got a result
[COLOR="Red"]Debug.Print rst!ProductDesc1
Debug.Print rst!ProductDesc2[/COLOR]
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing

and my stored procedure is

ALTER PROCEDURE [dbo].[spItemDesc]

	@ItemNo varchar(200) ,

AS
BEGIN

	set nocount on ;

select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo



END

the error comes on debug.print(rst!ProductDesc1)
and it says "Item not found in the collection"

?

Thanks in advance

 

[hfs]

code worked,forgot to write down the database name in connection string!
....

 

[jdraw]

Glad you got it sorted.

The guy with all the smarts on Stored Procedures and related is Michael Lueck

For links to his samples and write-ups
see http://www.access-programmers.co.uk/forums/showthread.php?t=257434