Microsoft Access 2007 tutorial:SQL update data


SQL update data

III.2. Update Data

The syntaxes of updating and inserting data can be executed by using Execute method of database object. To update data in a table, SQL uses the following Syntax:

UPDATE Table_Name SET field1=val1,field2=val2,... 

For example, edits an existing record that has BookID equal B004 of the Book table by editing the book's title from "C# Programming Language" to "C Programming Language". The new title was edited in the text box title of the Book form. See the figure:

Microsoft Access 2007 Updating Data 

After you clicked the Update command button, the book's title updated to "C Programming Language". You can see in the list:

Microsoft Access 2007 Result Updating Data 

To make the Book form can perform updating data, you have to type the following code in the Update command button:

Private Sub CmdUpdate_Click()

   Db.Execute "UPDATE TblBook SET PubID='" & txtPubID & "'," _
   & "' Title='" & txtTitle & "', ISBN='" & txtISBN & "'" _
   & "WHERE BookID='" & txtBookID & "'"
   MsgBox "Update Success!"
   lstViewData.Requery



End Sub

Or

Private Sub CmdUpdate_Click()

   Dim sqlText as String

   sqlText = "UPDATE TblBook SET PubID='" & txtPubID & "'," _
   & "' Title='" & txtTitle & "', ISBN='" & txtISBN & "'" _
   & "WHERE BookID='" & txtBookID & "'"
   MsgBox "Update Success!"
   lstViewData.Requery

   Db.Execute sqlText


End Sub

Remark: If you want to type the code of UPDATE statement above in a single line, you can type the code below:

Private Sub CmdUpdate_Click()

   Db.Execute "UPDATE TblBook SET PubID='" & txtPubID & "',Title='" & txtTitle & "', ISBN='"  & txtISBN & "' WHERE BookID='" & txtBookID & "'"
   MsgBox "Update Success!"
   lstViewData.Requery


End Sub

You see the code of UPDATE statement above has two lines because the space for typing the code is not enough for one line. However, you must type it in one line. If so the error will occur.




Comments

CAPTCHA image



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.