Actualize a Balance field in a Access Table

[jeva39]

I have in my Access Database a table "Prestamos" (Loans) and a table "Clientes" (Users). When I create a Prestamo (Loan) I need to modify the Saldo (Balance) in Clientes. The DataBase have the relations created but I don't have experience in this type of query. Please, ¿what is the correct code to do this operation?. In my other tables I use DataRows but I don't need to actualize one table from another. Thanks for your help...

 

[jmcilhinney]

If you are only updating one row at a time, you can use:

Dim clientUpdateCommand As New OleDbCommand("UPDATE Clientes SET Saldo = " & <new balance> & " WHERE = <key field> = <key value>", <connection>)

clientUpdateCommand.ExecuteNonQuery()

where the values between "<" and ">" will have to be supplied by you. If you are updating multiple rows, use a DataSet or a DataTable and an OleDbDataAdapter like this:

Dim clientAdapter As New OleDbDataAdapter("SELECT <key field>, Saldo FROM Clientes", <connection>)
Dim clientTable As New DataTable("Clientes")

clientAdapter.Fill(clientTable)

'Update balances.

Dim clientUpdateCommand As New OleDbCommand("UPDATE Clientes SET Saldo = ? WHERE <key field> = ?", connection)

clientUpdateCommand.Parameters.Add("Saldo", OleDbType.Currency, 0 "Saldo")
clientUpdateCommand.Parameters.Add("<key field>", OleDbType.Integer, 0 "<key field>")
clientAdapter.UpdateCommand = clientUpdateCommand
clientAdapter.Update(clientTable)

 

[jeva39]

Thanks!. Really is only record at time. I think the first option resolve very well my problem and is simple for use Sql Transactions in this operation (Record the Loan and actualize the Client Balance). Regards...