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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
What does the procedure return?
Are you looking for some kind of success or failure message?
ASKER
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
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
ASKER
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 ?
Does anyone else know how to do this ?
How To Get Return Parameters from a Stored Procedure with DAO
http://support.microsoft.com/kb/168210
http://support.microsoft.com/kb/168210
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try posting your Stored Procedure and we can give you the exact code.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
More info and source: http://www.c-sharpcorner.com/UploadFile/gustavoperezv/DAOtoADO.NET09032005072624AM/DAOtoADO.NET.aspx
ASKER
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
ASKER