Command Change / Edit Record in Microsoft Accces and Visual Basic

After learning how to add records in a Microsoft Access database, in this post we will learn how to change records in a Microsoft Access database.

We will use the Update command statement is also part of the Sql Statement.

Update Statement Syntax

As for use Update statement syntax is as follows:

UPDATE table
SET newValue
WHERE criteria;

The parts of the update command statement is:

table
Name of the table where the data will be changed.
newValue
Expression that specifies the name of the field and the new value will be changed
criteria
The expression that determines which records will diubah.Hanya records that match the criteria that will be updated.

retrieve data from the table "TestTable" from the previous example, the fieldnya consists of :

The data are as follows:

From the example above table we will try to change the data for id " E.2001.00003 " , we will replace the EmployeeName from Cyhntia be Chyntia Wardani , EmployeeAddress from Mango Street to be Mango Street # 1 , EmployeeAge from 24 to 25 , JoinDate of 15 / 05 / 2001 to 15/07/2011 and certificate from True to False .

The syntax is as follows :

"Update TestTable Set EmployeeName = ' Cynthia Wardani ' , EmployeeAddress = ' Mango Street 1 ' , EmployeeAge = 25 , JoinDate = ' 15.07.2001 ' , Certificate = False Where Id = ' E.2001.00003 ' "

Note the value E.2001.00003 , Cyhntia Wardani , No. Mango Street . 1 And 07/15/2001 flanked by single quotes , because these values are of type string and date in fieldnya respectively , whereas 25 and False values are not enclosed in quotation one , because the values of type Number and Yes / No in its fields respectively .

In Insert Into Statement Example Source Program

Taking the form of an example of how to add a record source using insert into statement earlier , which has the following design .

Then double click the Update button , and type the following command :

Private Sub CmdUpdate_Click ( ByVal sender As System.Object , ByVal e As System.EventArgs ) Handles CmdUpdate.Click
VUbah Dim As String = " Update TestTable Set EmployeeName = ' " & TxtEmployeeName.Text & " ' , EmployeeAddress = ' " & TxtEmployeeAddress.Text & " ' , " & _
" EmployeeAge = " & TxtEmployeeAge.Text & " , JoinDate = ' " & DtJoin.Text & " ' , Certificate = " & Cmb_Certificate.Text & " Where Id = ' " & TxtEmployeeId.Text & " ' "

Using Conn As New OleDbConnection ( ConnStr )
Conn.open ( )
Using cmd As New OleDbCommand ( vUbah , Conn. )
Cmd.ExecuteNonQuery ( )
Cmd.CommandType = CommandType.Text
MsgBox ( " Record Updated " , MsgBoxStyle.Information , "Update Record " )
TxtEmployeeId.Text = " " : TxtEmployeeName.Text = " " : TxtEmployeeAddress.Text = " "
TxtEmployeeAge.Text = " " : Cmb_Certificate.Text = " "
end Using
end Using

end Sub

Once done , try to run your project by pressing the F5 key , then enter the value you want to change to the table , then click the Update button , if there is no error message will be a message box that confirmed " Record Updated " which indicates that the record has been changed / updated into a table .

Simple Projects with Source Code

Command Change / Edit Record in Microsoft Accces and Visual Basic