Help with SQL Stored Procedure and MS Access 2000

[jpack23]

Wrote a Stored Procedure that give a true(T) or False(F) if a record was transfered. In my Access Database I would like to display that T or F to the user. The stored Procedure works great...in query analyzer the "Results" column has the proper T or F

I cant get acces to give me the T or F.

Here is the code:

Stored Procedure:

CREATE PROCEDURE spTransfer
AS

Insert Into TableA
Select *
from TableB
Where not Exists (Select 1 from TableA Join TableB on TableA.ID = TableB.ID)

Select CASE WHEN @@ROWCOUNT = 0 Then 'F' Else 'T' End As 'Results'


GO


Access Code:

Private Sub cmdAdd_Click()

Dim Rs As Recordset
Dim Db As Database

Set Db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;dsn=Name;uid=ID;pwd=pwd")
Db.Execute "spTransfer", dbSQLPassThrough
Set Rs = Db.OpenRecordset(spTransfer, dbOpenSnapshot, dbSQLPassThrough)

strStatus = Rs(0)

If StrSatus = T Then
MsgBox "New Hires Addition Successful", vbOKOnly
Else
MsgBox "Record for this employee already exists", vbOKOnly
End If


End Sub

When I run the access application I get a "Item not found error at Rs(0)....tried to use Rs!Results and the same error comes up

Please help

Thank you very much

Joe

 

[PHV]

Just a though, isn't ADO best suited than DAO for this job ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jpack23]

I dont know which would be best for this. Can you show me how its done using DAO? Can someone look at my code and tell me what I am missing? It seems I am so close I just can get the Value in the result field into my access application.

Thank you