• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3824
  • Last Modified:

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

0
cgaDesign
Asked:
cgaDesign
  • 7
  • 4
  • 3
  • +2
3 Solutions
 
jefftwilleyCommented:
change this to true

qrysp.ReturnsRecords = False

Use your Query Def as the source of a recordset vs a simple execute.

Dim rs as DAO.recordset
Set db = CurrentDb()

Set qrysp = db.CreateQueryDef("SomeName")
qrysp.Connect = "ODBC;DSN=MYTEST;"
qrysp.SQL = "exec spPROC_Update '"  & pYYYYMM & "', '" & pGroup & "'"
qrysp.ReturnsRecords = True
qrysp.Close

Set rs = DB.OpenRecordset("YourQueryDefName",dbopensnapshot,dbseechanges)

if not rs.eof then intCnt = rs.Fields(0).value

rs.close
set rs = nothing

db.Close

J
0
 
cgaDesignAuthor Commented:
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 ?
0
 
jefftwilleyCommented:
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?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
cgaDesignAuthor Commented:
In this case the OUTPUT PARAMETER returned from the stored procedure is an Integer, but I may add more parameters later.  Thank you.
0
 
jefftwilleyCommented:
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
0
 
cgaDesignAuthor Commented:
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 ?
0
 
DhaestCommented:
How To Get Return Parameters from a Stored Procedure with DAO
http://support.microsoft.com/kb/168210
0
 
cgaDesignAuthor Commented:
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.
0
 
CodeCruiserCommented:
Here is an example with ADO

http://support.microsoft.com/kb/194792
0
 
Anthony PerkinsCommented:
Try posting your Stored Procedure and we can give you the exact code.
0
 
cgaDesignAuthor Commented:
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


0
 
DhaestCommented:
Capture SQL Server stored procedure output values with ADO
http://www.a1vbcode.com/vbtip-118.asp
0
 
cgaDesignAuthor Commented:
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.
0
 
DhaestCommented:
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
0
 
DhaestCommented:
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
0
 
cgaDesignAuthor Commented:
Grading the solution is not easily relevant, when multiple solutions were involved;  all solutions were not of the same usefulness or clarity.
0
 
CodeCruiserCommented:
I have posted an example which uses ADO. Have a look above http:#a32677766
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now