MS Access : Source Code Lengkap Input, Edit, Hapus & Preview

Imports System.Data.OleDb

Imports System.Data

Imports Access = Microsoft.Office.Interop.Access

Imports Microsoft.Office


Public Class Form2

    Dim idbarang As String

    Dim namabarang As String

    Dim hargabarang As String

    Dim namaprodusen As String

    Dim oledbKoneksi As OleDbConnection = New OleDbConnection

    Dim oledbPerintah As OleDbCommand = New OleDbCommand

    Dim lokasiFile As String = "D:\fileAccess\"

    Dim namaFile As String = "Database1.accdb"

    Dim stringKoneksi As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lokasiFile & namaFile & ";" & "Persist Security Info=False;"

    Dim adapter As New OleDbDataAdapter


    Sub tampilIsiGrid()

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "Select * From databarang"

        adapter.SelectCommand = oledbPerintah

        Dim ds As DataSet

        ds = New DataSet

        adapter.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

        oledbKoneksi.Close()


    End Sub


    Sub autoID()

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "SELECT * FROM databarang ORDER BY idbarang desc"

        Dim baca As OleDbDataReader = oledbPerintah.ExecuteReader

        baca.Read()

        If Not baca.HasRows Then

            TextBox1.Text = "DB" + "0001"

        Else

            TextBox1.Text = Val(Microsoft.VisualBasic.Mid(baca.Item("idbarang").ToString, 4, 3)) + 1

            If Len(TextBox1.Text) = 1 Then

                TextBox1.Text = "DB000" & TextBox1.Text & ""

            ElseIf Len(TextBox1.Text) = 2 Then

                TextBox1.Text = "DB00" & TextBox1.Text & ""

            ElseIf Len(TextBox1.Text) = 3 Then

                TextBox1.Text = "DB0" & TextBox1.Text & ""



            End If

        End If

        oledbKoneksi.Close()

    End Sub


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

        'Data Baru

        autoID()

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""



    End Sub

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

        'Simpan Data

        idbarang = TextBox1.Text

        namabarang = TextBox2.Text

        hargabarang = TextBox3.Text

        namaprodusen = TextBox4.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "INSERT INTO databarang(idbarang,namabarang,hargabarang,namaprodusen) values" & "('" & idbarang & "', '" & namabarang & "', '" & hargabarang & "', '" & namaprodusen & "')"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()

        oledbKoneksi.Close()


    End Sub

    Private Sub Form2_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        'Perintah pertama yg dijalankan ketika program di load

        autoID()

        tampilIsiGrid()


    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        'Tampil data dari datagridview ke textbox

        Dim row As DataGridViewRow = DataGridView1.CurrentRow

        Me.TextBox1.Text = row.Cells(0).Value.ToString

        Me.TextBox2.Text = row.Cells(1).Value.ToString

        Me.TextBox3.Text = row.Cells(2).Value.ToString

        Me.TextBox4.Text = row.Cells(3).Value.ToString

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click

        'Update Data

        idbarang = TextBox1.Text

        namabarang = TextBox2.Text

        hargabarang = TextBox3.Text

        namaprodusen = TextBox4.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "UPDATE databarang SET namabarang = '" & namabarang & "', hargabarang = '" & hargabarang & "', namaprodusen = '" & namaprodusen & "' WHERE idbarang = '" & idbarang & "'"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()

        oledbKoneksi.Close()


    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        'Hapus data

        idbarang = TextBox1.Text

        namabarang = TextBox2.Text

        hargabarang = TextBox3.Text

        namaprodusen = TextBox4.Text

        oledbKoneksi.ConnectionString = stringKoneksi

        oledbKoneksi.Open()

        oledbPerintah.Connection = oledbKoneksi

        oledbPerintah.CommandText = "DELETE * FROM databarang WHERE idbarang = '" & idbarang & "'"

        oledbPerintah.ExecuteNonQuery()

        oledbKoneksi.Close()

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        autoID()

        DataGridView1.Refresh()

        tampilIsiGrid()

        oledbKoneksi.Close()

    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click

        'Menampilkan Report MS Access

        Dim MSA As New Access.Application

        MSA.Visible = True

        MSA.OpenCurrentDatabase("D:\fileAccess\Database1.accdb", False)

        MSA.Application.DoCmd.OpenReport("reportDataBarang", Access.AcView.acViewPreview)

    End Sub
End Class