Microsoft Access 2010 tutorial:Update data with DAO


Update data with DAO

IV. Update data

The updating and appending data can be performed by using Execute method of database object. To update data in a table, use the same syntax of appending data to a table. However, the Query argument of the syntax you must use the UPDATE statement of the SQL that has the following syntax:

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

For example, edit an existing record that has BookID equal to B004 of the TblBook by editing the book's title from "C# Programming Language" to "C Programming Language". The new title was edited in the Title text box of the frmBook. 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 enable the frmBook to perform updating data, you have to type the following code between Private Sub CmdUpdate_Click() and End Sub block:

Option Compare Database

Dim Db As DAO.Database

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




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.