Insert record in MS Access using Visual Basic

Insert Record
Insert Record

In our previous tutorial, we have discussed the step by step procedure on how to connect visual basic to ms access. This time we’re going to learn how to insert a record in our database.

We will provide you the step by step procedure on how to do that and source code will be provided for your reference.

Since this is the continuation of the previous project, kindly download the source code of the previous tutorial. (How to connect Visual Basic to MS Access).

After you have downloaded the source code, kindly follow the instructions below:

1. Open the database (Data.mdb) and create a new table (in the menu, select Create and click the Table icon).

2. Save the table as tblStudent and enter the following field:

Field Name            Data Type

ID – Autonumber (this will auto increment by 1)
studentName – Text
studentAge – Number
studentContact – Number
studentAddress – Text

Student Table
Student Table

3. Open Project1.vbp and add a new form.

4. To add a new form, click Project in the menu bar and click Add Form select Form and click Open. Another way of adding new form is to right click the project explorer and select Add and click Form.

5. Rename the form into InsertRecordFrm.

6. Open the form and add 4 Textbox and 4 Labels and 1 Command Button.

7. Rename the Textbox into: txtStudentName, txtStudentAge, txtStudentContact, txtStudentAddress

8. Rename the Command Button into cmdSave and change the caption into Save.

9. Insert the code below in the form. (right click the InsertRecordFrm and click View Code and paste the code below.)

Sub InsertRecord()

If rs.State = adStateOpen Then rs.Close

sql = "Select * from tblStudents Where studentName='" & txtStudentName.Text & "'"

rs.Open sql, conn

If rs.RecordCount >= 1 Then

MsgBox "Duplicate Name Found.Please enter another name", vbInformation, ""

Exit Sub

End If

With rs

.AddNew

!studentName = txtStudentName.Text

!studentAge = txtStudentAge.Text

!studentContact = txtStudentContact.Text

!studentAddress = txtStudentAddress.Text

.Update

End With

MsgBox "New Record Successfully added", vbInformation, ""

End Sub

Note 1: The code above will add a new record to our tblStudent.

sql = "Select * from tblStudents Where studentName='" & txtStudentName.Text & "'"

rs.Open sql, conn

If rs.RecordCount >= 1 Then

MsgBox "Duplicate Name Found.Please enter another name", vbInformation, ""

Exit Sub

End If

The line of codes states that the database will not accept the record if the name of the student is already in the database and will prompt a message  “Duplicate Name Found. Please enter another name” and ignore the rest of the code.

10. The InsertRecord 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 Command Button click event

The code will look like this:

Private Sub cmdSave_Click()
InsertRecord
End Sub

11. We are now ready to test the application. Kindly press F5.

12. Fill up all the textbox. The message will appear “New Record Successfully added” indicating that we have added new record in our database.

13. Open the tblStudent and notice that the data encoded in the form has shown in the table.

 

Successfully added new record
Successfully added new record

Note 2: to prevent errors, kindly close the table(s) in your ms access database.

, , , ,

Post navigation

2 thoughts on “Insert record in MS Access using Visual Basic

  1. Hi, Where I will add this code:
    Please add some screenshots thanks

    sql = “Select * from tblStudents Where studentName='” & txtStudentName.Text & “‘”

    rs.Open sql, conn

    If rs.RecordCount >= 1 Then

    MsgBox “Duplicate Name Found.Please enter another name”, vbInformation, “”

    Exit Sub

    End If

    1. Good day

      you don’t need to paste it anymore because it’s already in the InsertRecord procedure.

      that line of codes prevent duplication of records in the database

      happy coding

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.