Simple Add, Edit, Delete VB .Net 2005 DB Access 2007 (accdb)

in this tutorial Visual Studio 2005, we will share about How to make form Add, Edit, Delete VB 2005 with database Access 2007 (accdb). for this tutorials, also can use in Database Microsoft Access 2010 or 2013, because the extension is same there is accdb

We have Database with name : DB.accdb
and we create also Table with name : TBL_CUSTOMER
Open your VB 2005
Cretae design form like below picture :

Simple Add, Edit, Delete VB .Net 2005 DB Access 2007 (accdb)
Place source code at Form1 :
Imports System.Data.OleDb
Public Class Form1
    Dim CONN As OleDbConnection
    Dim CMD As OleDbCommand
    Dim DS As New DataSet
    Dim DA As OleDbDataAdapter
    Dim RD As OleDbDataReader
    Dim DBLocation As String
    Sub DBConn()
        DBLocation = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DB.accdb"
        CONN = New OleDbConnection(DBLocation)
        If CONN.State = ConnectionState.Closed Then
            CONN.Open()
        End If
    End Sub
    Sub EmptyForm()
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()
        TextBox1.Focus()
    End Sub

    Sub TampilGrid()
        DA = New OleDbDataAdapter("select * from TBL_CUSTOMER", CONN)
        DS = New DataSet
        DA.Fill(DS, "TBL_CUSTOMER")
        DataGridView1.DataSource = DS.Tables("TBL_CUSTOMER")
        DataGridView1.ReadOnly = True
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If TextBox1.Text.Length < 6 Or TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Then
            MsgBox("Data incompleted, Make sure all form is filled and customer id 6 digits")
            Exit Sub
        Else
            Call DBConn()
            CMD = New OleDbCommand("Select * from TBL_CUSTOMER where Customer_id='" & TextBox1.Text & "'", CONN)
            RD = CMD.ExecuteReader
            RD.Read()
            If Not RD.HasRows Then
                Dim simpan As String = "insert into TBL_CUSTOMER values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"
                CMD = New OleDbCommand(simpan, CONN)
                CMD.ExecuteNonQuery()
                MsgBox("Add data successed", MsgBoxStyle.Information, "Information")
            Else
                Dim edit As String = "update TBL_CUSTOMER set Name='" & TextBox2.Text & "',Address='" & TextBox3.Text & "',Phone='" & TextBox4.Text & "' where kodeAnggota='" & TextBox1.Text & "'"
                CMD = New OleDbCommand(edit, CONN)
                CMD.ExecuteNonQuery()
                MsgBox("Edit data successed", MsgBoxStyle.Information, "Information")
            End If
            Call TampilGrid()
            Call EmptyForm()
        End If
    End Sub

    Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        If TextBox1.Text = "" Then
            MsgBox("Customer id is empty, please filled it")
            TextBox1.Focus()
            Exit Sub
        Else
            If MessageBox.Show("Are you sure want to delete..?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                Dim hapus As String = "delete * from TBL_CUSTOMER where Customer_id='" & TextBox1.Text & "'"
                CMD = New OleDbCommand(hapus, CONN)
                CMD.ExecuteNonQuery()
                Call TampilGrid()
                Call EmptyForm()
                MsgBox("Deleted successed", MsgBoxStyle.Information, "Information")
            Else
                Call EmptyForm()
            End If
        End If
    End Sub

    Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Call EmptyForm()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.Close()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call DBConn()
        Call TampilGrid()
        Call EmptyForm()
    End Sub

    Private Sub TextBox1_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        TextBox1.MaxLength = 6
        If e.KeyChar = Chr(13) Then
            Call DBConn()
            CMD = New OleDbCommand("Select * from TBL_CUSTOMER where Customer_id='" & TextBox1.Text & "'", CONN)
            RD = CMD.ExecuteReader
            RD.Read()
            If Not RD.HasRows Then
                TextBox2.Text = ""
                TextBox3.Text = ""
                TextBox4.Text = ""
                TextBox2.Focus()
            Else
                TextBox2.Text = RD.Item("Name")
                TextBox3.Text = RD.Item("Address")
                TextBox4.Text = RD.Item("Phone")
                TextBox2.Focus()
            End If
        End If
    End Sub

    Private Sub TextBox2_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress
        TextBox2.MaxLength = 50
        If e.KeyChar = Chr(13) Then TextBox3.Focus()
    End Sub

    Private Sub TextBox3_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox3.KeyPress
        TextBox3.MaxLength = 50
        If e.KeyChar = Chr(13) Then TextBox4.Focus()
    End Sub
    Private Sub TextBox4_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox4.KeyPress
        TextBox4.MaxLength = 15
        If e.KeyChar = Chr(13) Then Button1.Focus()
    End Sub
End Class

and the Last you can Run your Project
You can click here to download Project above / source code

Subscribe to receive free email updates:

3 Responses to "Simple Add, Edit, Delete VB .Net 2005 DB Access 2007 (accdb)"