Updating a record in MS Access using Visual Basic
Welcome to our next tutorial in Visual Basic. This tutorial will teach us the procedures on how to update a record in MS Access.
This is the continuation of our previous tutorial which is Inserting a record in MS Access database.
Let’s do first a checklist:
- Connect Visual Basic to MS Access – we have already performed that, just in case you have missed the lesson kindly visit the page. (How to connect Visual Basic to MS Access)
- Insert a record in MS Access through Visual Basic – we have done on this part.
We will now learn how to update a record in MS Access through Visual Basic. Kindly download the source code of the previous tutorial (Insert a record in MS Access through Visual Basic) to be able to follow the procedures correctly.
1. Kindly open Project1.vbp
2. Open the InsertRecordFrm, this is the form where we can insert a record in our database and at the same time the form where we can update the record.
3. In the previous tutorial we did not include ID in inserting a record because it has an attribute of AutoNumber which automatically assigns a number to our record. This time we’re going to need the ID for updating a record. Kindly add a TextBox and name it as txtID and add a Label with the caption ID No.
4. Next, add a Command Button and name it as cmdUpdate with the caption Update. It should be like this:
5. Before we can update 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
6. The lines of code above will populate the text fields, meaning the record from our table students will be filled in the text fields.
7. Next is we are going to construct an update function. Insert the code below in the form. (right click the InsertRecordFrm and click View Code and paste the code below.)
Sub UpdateRecord()
If rs.State = adStateOpen Then rs.Close
sql = "Update tblstudent set studentname='" & txtStudentName.Text & "', StudentAge=" & txtStudentAge.Text & ", StudentContact=" & txtStudentContact.Text & ",StudentAddress='" & txtStudentAddress.Text & "' Where id=" & txtID.Text & ""
rs.Open sql, conn
MsgBox "Record(s) successfully updated", vbInformation, ""
End Sub
Note: whatever value you entered in the text fields will replace value of the record in the database.
8. The UpdateRecord 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 Update command button that we have added in the form recently.
The code will look like this:
Private Sub cmdUpdate_Click()
UpdateRecord
End Sub
9. We are now ready to test the application. Kindly press F5.
10. A message will appear (Record(s) successfully updated) that signifies that we have updated the record. Run the program again to see if the record really changes.
Note 1: ID field can’t be updated because its data type is AutoNumber. AutoNumber is a unique numeric value that MS Access automatically inserts when a record is added.
Note 2: Updating of record(s) is very important function in any system or application because it allows you to modify the record(s) in case you have entered data incorrectly.
Next tutorial is on how to delete a record from MS Access database.
