Link to home
Start Free TrialLog in
Avatar of cgaDesign
cgaDesign

asked on

Return SQL Stored Procedure OUTPUT parameter to MS Access

I'm using MS Access 2003 and SQL Server 2005. I have a working Stored Proc that receives 2 parameters, and updates many records in a table.  My included MS Access code that calls this SP works fine. I would now like to add an OUTPUT parameter to the SP (which I know how to do), and then return the OUTPUT parameter to Access (which I am asking for help with).  I will set the OUTPUT parm in the SP to @@RowCount.

So how can I capture the OUTPUT parameter from the SP in Access code ?  Thank you.
' These are global
pYYYYMM = "201002"
pGroup = "NY"

Dim pRowsUpdated As Integer
pRowsUpdated = 3

Dim db As DAO.Database
Dim qrysp As QueryDef
Dim intCnt As Integer

Set db = CurrentDb()
Set qrysp = db.CreateQueryDef("")
qrysp.Connect = "ODBC;DSN=MYTEST;"

qrysp.SQL = "exec spPROC_Update '" _
  & pYYYYMM & "', '" & pGroup & "'"

qrysp.ReturnsRecords = False
qrysp.Execute

intCnt = pRowsUpdated

qrysp.Close
db.Close

Open in new window

SOLUTION
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cgaDesign
cgaDesign

ASKER

Thank you for your response, and your solution, which is actually a workaround, is good to know.  But it does not answer my question. I need to know how to capture the OUTPUT PARAMETER from a SQL stored procedure, into Access code.  Any thoughts on that ? Is it even possible ?  Maybe I should use ADO ?
the output parameter is simply a string of data is it not?

What does the procedure return?

Are you looking for some kind of success or failure message?
In this case the OUTPUT PARAMETER returned from the stored procedure is an Integer, but I may add more parameters later.  Thank you.
in my example above, I showed you how you can assign your return value into your Variable.

intCnt = rs.Fields(0).value

Are you looking for another solution?

There are a lot of ways to get data from SQL back into Access. It just depends on what you're trying to do.

Are you thinking that you can retrieve the INT from the querydef object? If so, that isn't possible.

What exactly does this do?

exec spPROC_Update '"  & pYYYYMM & "', '" & pGroup & "'"

J
I still am trying to find out how to return a Parameter from a SQL Server stored procedure back to the MS Access code module that is executing the stored procedure.
Does anyone else know how to do this ?
Avatar of Dirk Haest
How To Get Return Parameters from a Stored Procedure with DAO
http://support.microsoft.com/kb/168210
Dhaest, thank you for responding.  Your link is very useful ... I now see for sure that DAO does not, in a straightforward way, allow you to return an OUTPUT parameter from a stored procedure ... that is important to know.  It is also good to know about the workaround  using other SPs ... but this seems as quite a kludgy workaround ... and I hope to find a cleaner and more understsandable way to do this. This is important if others work on my software later.

On my own, I am getting close to returning a parameter using ADO, which is now the preferred object model to use over DAO.  Do you have a link for ADO re: SQL parameters, or know how to use ADO to return an SQL parameter ?  
Thanks very much.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try posting your Stored Procedure and we can give you the exact code.
acperkins, thanks much, but I'm not looking for someone to write all the code for me ... just how to return OUTPUT parameters into MS Access.  I'm currently thinking that ADO might be the cleanest way, but not sure yet.

Here is what is needed for Access: DSN=MYTEST (or maybe a DSN-Less connection could work)
  Stored Procedure is spPROC_UPDATE
  Two OUTPUT parameters in the SP:  parm_Value1, parm_Value2


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much to all ... I feel strongly that the ADO links with solve my problem.  If not, I'll start a new question. The ADO approach seems to be the most robust, and seems like the way Microsoft intended for this scenario.
DAO is an older technology in the microsoft roadmap :)

From DAO to ADO
http://www.databasejournal.com/features/mssql/article.php/1490571/From-DAO-to-ADO.htm
Software systems dedicated specifically to the management of data have been in existence only for 40 years, and still  some some applications do not make use of them. In the absence of specific software, data management is is performed by means  of traditional programming languages, for example C, Fortran or Visual Basic. Visual Basic 3.0 was the first version with database access capabilities. Data Access Object (DAO) enabled programmers to access local databases in the Microsoft Jet Database Engine format, which were primarily Indexed Sequential Access Method (ISAM) files. After DAO came RDO  and then ActiveX Data Objects (ADO). These data access technologies were designed for a client / server paradigm. However  the tendency of distributed computing forced the development of a new technology  to solve the problems of  data manipulation on a n-tier architecture. ADO.NET is the evolution of ADO and its components have benn designed to function properly on a n-tier architecture.

More info and source: http://www.c-sharpcorner.com/UploadFile/gustavoperezv/DAOtoADO.NET09032005072624AM/DAOtoADO.NET.aspx
Grading the solution is not easily relevant, when multiple solutions were involved;  all solutions were not of the same usefulness or clarity.
I have posted an example which uses ADO. Have a look above http:#a32677766