SQL UPDATE with RETURN

Question

Is it possible to make an SQL statement or procedure to or in MS Access that updates a field in a table and returns the result value?

I am using VB.net and ADODB.

If the name of the table is tblAccount, the field is Saldo and The Id of the table is AccountNo the amount to add is AddValue, the wised record has the AccountNo is  ActAccountno:

This is not valid MS Access SQL but I hope it explain my goal.

"UPDATE tblAccount  " &

"SET [Saldo] = [Saldo] + " & AddValue.tostring &" " &

"WHERE AccountNo = " & ActAccountNo.tostring & " " &

"RETURN Saldo;"

 

Then send the SQL-command with

ADODB.Recordset.open

(or ADODB.Connection.Execute)

Receiving the results maybe like a recordset(?).

 

I know I can accomplish the same with two or three calls to the database.

Answer 1

No, that isn't possible. You have to perform the update first, then retrieve the new value.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

It would be possible if you use a recordset. In VBA it would be something like:

dim rs as dao.recordset
dim curSaldo as currency
set rs=currentdb.openrecordset("select * from tblAccount where AccountNo=123", dbOpenDynaset)
if not rs.EOF then
 rs.Edit
 rs!Saldo = rs.Saldo + 456
 rs.Update
 curSaldo = rs!Salso
end if

rs.Close
set rs=Nothing

---

-Tom. Microsoft Access MVP