This tutorial in Visual Basic 6 will teach us on how to delete a record in MS Access.
This is actually the Part 4 of our Visual Basic 6 tutorials
Let us first review what we have learned in Visual Basic:
1. We were able to connect our Visual Basic into MS Access.
2. We have learned how to insert a record in MS Access.
3. We have also learned how to update the record in the database.
Kindly download the source code of the previous tutorial (Updating a record in MS Access using Visual Basic) to be able to follow the procedures correctly.
The following are the procedures on deleting a record:
1. After you have downloaded the file, extract it and open Project1.vbp.
2. Open the InsertRecordFrm. Previously, this is the same form where we can insert and update a record.
3. Kindly add a command button and name it as cmdDelete with a caption Delete. Take note that caption of the button is different from name of the object.
4. Before we can delete a record we must first retrieve the record from the database. On the Load() event of our form kindly insert the following codes:
If rs.State = adStateOpen Then rs.Close
sql = " SELECT * From tblstudent"
rs.Open sql, conn
If rs.RecordCount > 0 Then
rs.MoveFirst
txtID.Text = rs(0).Value
txtStudentName.Text = rs(1).Value
txtStudentAge.Text = rs(2).Value
txtStudentContact.Text = rs(3).Value
txtStudentAddress.Text = rs(4).Value
End If
5. The code above will retrieve the record in the database and place it in the text fields.
6. Next is we are going to create a function and name it as DeleteRecord. Kindly copy the codes below in our InsertRecordFrm form. To paste the code just right click the InsertRecordFrm and click View Code and paste the code below.
Function DeleteRecord()
If rs.State = adStateOpen Then rs.Close
sql = "Delete * From tblstudent Where ID='" & txtID.Text & "'"
rs.Open sql, RSconn
MsgBox "Record(s) deleted.", vbInformation, ""
End Function
7. The DeleteRecord is a procedure or a sub routine in visual basic, this function will not be executed unless this function will be call. We’re going to place the function in our cmdDelete command button that we have added in the form recently.
The code will look this:
Private Sub cmdDelete_Click()
If vbYes = MsgBox("Are you sure you want to delete the record?", vbQuestion + vbYesNo, "") Then
DeleteRecord
Unload Me
InsertRecordFrm.Show
End If
End Sub
Based on the code above, the system will ask you first if you really want to delete the record, if yes then the record will be deleted from the database.
8. Test the project by pressing F5.
9. A message will appear (Record(s) deleted.) signifying that the record was successfully removed from the database.
The record will be deleted based on the ID, meaning all of the records (name, address, age, contact) of that certain ID will also be removed from the database
Note: deleting a record is a crucial part in an information system because those records may not be recovered, so a piece of advice, always backup your database.
Next tutorial is on how to populate listview from database.
