Microsoft Access 2010 tutorial:delete data with DAO


Delete data with DAO

V. Delete data

Delete is the process of removing a specific row or many rows from a table of  the database. To delete a row from the table, you need to use the SQL statement of deleting data that has the following syntax.

DELETE FROM Table_Name

WHERE condition

If you want to delete all rows of the table, you need to erase the WHERE clause.

For example, delete an existing record that has BookID equal to B005 of the TblBook by clicking B005 in the list and then clicking the Delete command button on the frmBook. See the figure:

Access deleting data

After you clicked the Delete command button, the record is removed from the list. You can see in the list:

 Access result of deleting data

To make the frmBook can perform the data deletion, you have to type the following code in the Delete command button procedure:

Option Compare Database

Dim Db As DAO.Database

Private Sub CmdDelete_Click()

   Db.Execute "DELETE FROM TblBook WHERE" _
   & "'BookID='" & lstViewData.Column(0, lstViewData.ListIndex + 1) & "'"
   MsgBox "Delete Success!"
   lstViewData.Requery

End Sub

Or

Private Sub CmdDelete_Click()

   Dim sqlText As String

   sqlText = "DELETE FROM TblBook WHERE" _
   & "'BookID='" & lstViewData.Column(0, lstViewData.ListIndex + 1) & "'"
   MsgBox "Update Success!"
   lstViewData.Requery

   Db.Execute sqlText

End Sub

Remark: If you want to type the code of DELETE statement above in a single line, it can be done as shown below:

Private Sub CmdDelete_Click()

   Db.Execute "DELETE FROM TblBook WHERE BookID='" & lstViewData.Column(0,    lstViewData.ListIndex + 1) & "'"
   MsgBox "Delete Success!"
   lstViewData.Requery

End Sub

You see the code of DELETE 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.