Return SQL Stored Proc Output Parameter to Access

[moorejp]

Using Access 97. I have a module that opens querydef to call a SQL stored procedure to insert a new record. That proc has an output parameter (@CAMP_ID) to return the new record's ID. Once I execute the SQL statement from within Access, I need to bring that output parameter back into Access for further processing, but I'm stuck. Code example is below...

Dim strSQL As String
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qdfTemp As QueryDef

strSQL = "EXEC spu_NewCampaign blah, blah, @CAMP_ID OUTPUT"

Set wrkODBC = CreateWorkspace("", CurrentUser, "", dbUseODBC)

Set conPubs = wrkODBC.OpenConnection(blah, , , blah)

Set qdfTemp = conPubs.CreateQueryDef("&quot

With qdfTemp
.Prepare = dbQUnprepare
.SQL = strSQL
.Execute
'Here's where I need @CAMP_ID returned !!!
End With


Any ideas where to go next, or do I need to back up and rethink something? Any help is greatly appreciated!

--John

 

[mac318]

You didn't specify the location of this module. Is it in a class, standard or form module? I suggest running your code in the form module or call it from a class module from a form, add a recordset to access the new record, and place required result in a text box control on the form from the recordset.

mac318

 

[moorejp]

Thanks for the advice & sorry for the long delay in responding. As you suggested, I was able to place the code in the form module, use the ReturnsRecords querydef property to set up a new recordset to access the new record, and hold the returned value for later use.

It works like a champ now. Once again, many thanks!