none
SQL UPDATE with RETURN RRS feed

  • 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 recordsethttps://d.adroll.com/cm/index/outhttps://d.adroll.com/cm/n/out(?).

     

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

    Sunday, May 21, 2017 5:44 PM

Answers

  • 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

    • Marked as answer by hjl1 Monday, May 22, 2017 4:24 AM
    Sunday, May 21, 2017 8:49 PM

All replies

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

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

    Sunday, May 21, 2017 5:59 PM
  • 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

    • Marked as answer by hjl1 Monday, May 22, 2017 4:24 AM
    Sunday, May 21, 2017 8:49 PM