Access DoCmd.runSQL running a SQLServer stored procedure

pjrv2
pjrv2 used Ask the Experts™
on
I am using an Access front end to a SQLServer database. Some VBA uses DoCmd.runSQL to run stored procedures in the database. What is the best way to capture the return from the stored procedure?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Access debugger?
What kind of query are you using?
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
What is returned from the Stored Procedure? If it's a Recordset, you can just do this:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset(YourQueryName)

If it's an Output param, you're probably better off using an ADODB.Command object.

Author

Commented:
The stored procedure is just returning a status (success/fail)
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
But how does it do that? Does it return it via an Output parameter?

Author

Commented:
The Stored Procedure ends with Return 0 or Return -1

Author

Commented:
Further research based on the ADODB.Command suggestion worked out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial