TSQL & VBA - Output / Return Value?

Question

I seem to be having difficulty getting a return value from my SP into VBA.

I have issued a RETURN in my T-SQL which is fine, but I can't seem to work out how to get the value in the VBA?

Dim qd As QueryDef
Dim db As DAO.Database
Dim vRate As Integer
vRate = 3
Set db = CurrentDb
Set qd = db.QueryDefs("spCC_UpdateRAG")

' inital rating
qd.SQL = "EXEC spCC_UpdateRAG @Col = 'Rating', @RAG = " & Nz(vRate, "NULL") & ", @Case_ID = 36"
qd.Execute

This works fine other than I can't seem to get the return value?

So I thought I would try with OUPUT params instead, but SQL moans if you don't assign them a value, which makes no sense as they are output not input params?

So I tried this in my T-SQL...

-- return failure (false)   
SET @Result = 0

SELECT @Result

but that gives me a result set return which I don't want and a column with no name, so if I have to do this..

-- return failure (false)   
SET @Result = 0

SELECT @Result AS Result

What's the point? I might as well do...

SELECT 0 AS Result

And either way I'm getting back a recordset and I don't want one, I just want to return a bit (true/false)

Was the SP successful or not, how do I do this and get the value in my VBA code?

Your input is appreciated. 1DMF

See how the argument adParamReturnValue was used in the CreateParameter method of the ADODB.Command object in this SO answer. — Mike
– Mike, Commented Jul 22, 2014 at 15:38
? I'm not using ADODB, are you saying it isn't possible using what I have been told by the access community to use for all my DB activities -> DAO. — 1DMF
– 1DMF, Commented Jul 22, 2014 at 15:44
Well that's not a question for which I can give a one sentence answer. The short answer is in the case of DAO vs ADODB "Always use X" is an oversimplification. You can almost always get away with standing by one option and usually won't notice any real difference. I suggest you do some googling. — Mark Balhoff
– Mark Balhoff, Commented Jul 22, 2014 at 15:53
Regarding "I have been told by the access community to use [DAO] for all my DB activities", DAO is best for the Jet/ACE database engine. ADODB allows you to tap into SQL Server directly, so I recommend it in this situation, which agrees with the general consensus. — Mike
– Mike, Commented Jul 22, 2014 at 16:14
Thanks Mike, I'll look into it, though I would point out that I use nothing but MS SQL via linked tables or SP's and pass-through queries with DAO and have done for many years, hence wondering why people claim for SQL you need ADODB, when I know you don't. It's always difficult to sift though a sea of peoples opinions to get to the truth and what is actually needed to resolve the problem. — 1DMF
– 1DMF, Commented Jul 23, 2014 at 8:54

1DMF · Accepted Answer · 2014-07-24 11:28:36Z

1

In the end I went for the following solution as I couldn't seem to get either a return value or a parameter collection back in my VBA code. (perhaps a limitation of a pass-through query with DAO?)

The SP simply returns a recordset, 1 row / 1 column (Result = 0|1)

Then in the VBA

Dim rs As DAO.Recordset
Dim qd AS QueryDef
Dim bOK As Boolean

Set qd = CurrentDb.QueryDefs("my_pass_through_query")
qd.SQL = "EXEC my_SP @arg1 = 'value1', @arg2 = value2"
Set rs = qd.OpenRecordset(dbOpenSnapshot)
bOK = rs.Fields("Result")

Set rs = Nothing
Set qd = Nothing

It's a little convoluted for what I was trying to achieve but it works.

answered Jul 24, 2014 at 11:28

1DMF

2,1754 gold badges15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL & VBA - Output / Return Value?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related