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(""
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
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(""
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