Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting RETURN VALUE with Pass Thru Query

Status
Not open for further replies.

denaliak

MIS
May 26, 2004
5
US
I have a program that I inherited that uses pass thru queries to update a SQL table. Sometimes the query will fail but this is never reported back to Access. How can I use the RETURN Value of SQL to send a value back to MS Access? What is the Access code to receive this value? It is written in Access 2000 but could convert to 2003 if necessary. The system uses both ODBC to SQL and linked tables.

Thanks
 
denaliak

Take a look on the Execute Method of an ADO connection along with RecordsAffected optional long variable with options adCmdText + adExecuteNoRecords. Something like

Dim myRecordsAffected As Long
SQLCnxn.Execute "UPDATE ....", myRecordsAffected, 129

SQLCnxn = An open connection to your SQL server
myRecordsAffected = It is what it says
adCmdText = SQL statement is evaluated as text
adExecuteNoRecords = Do not return any records if any

So, if myRecordsAffected = 0 nothing has changed!
 
Thanks. This is a good idea when running the query directly from the Access code but I need to know if rowback occured within a SQL stored procedure when several queries are run. Looking for a way of checking the @@error and returning that value with the RETURN @@error statement.
 
this is my PROCEDURE
Code:
create PROCEDURE test
@msg varchar(20) output
as
Select @msg='testttttt'
this is my pass thru query
Code:
DECLARE @outputvar varchar(20)
EXECUTE test @outputvar OUTPUT
SELECT @outputvar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top