Visual Basic 2008 / 2010 Tutorials
www.visual-basic-tutorials.com

 

Index

Write to Access (2007) table from visual basic form

In this tutorial, we will write to Access Database. In this tutorial I create a table with the following fields:

and I have the following form:

Code:

(Note that you have to add the following reference to your procject: Microsoft Access 12.0 Object Library) To do that:

Go to Project - Add Reference - Click on COM tab, scroll to Microsoft Access 12.0 Object Library

Imports Access = Microsoft.Office.Interop.Access

Imports System.Data

Imports System.Data.OleDb

Public Class Form1

    Dim provider As String

    Dim dataFile As String

    Dim connString As String

    Dim myConnection As OleDbConnection = New OleDbConnection

 

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click

        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="

        dataFile = "C:\Users\Jimmy\Documents\Test Database.accdb"

        connString = provider & dataFile

        myConnection.ConnectionString = connString

 

        myConnection.Open()

        Dim str As String

        str = "insert into Items ([UPC], [Item], [Cost], [Price], [Quantity]) values (?, ?, ?, ?, ?)"

        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

        cmd.Parameters.Add(New OleDbParameter("UPC", CType(TextBox1.Text, String)))

        cmd.Parameters.Add(New OleDbParameter("Item", CType(TextBox2.Text, String)))

        cmd.Parameters.Add(New OleDbParameter("Cost", CType(TextBox3.Text, String)))

        cmd.Parameters.Add(New OleDbParameter("Price", CType(TextBox4.Text, String)))

        cmd.Parameters.Add(New OleDbParameter("Quantity", CType(TextBox5.Text, String)))

        Try

            cmd.ExecuteNonQuery()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        TextBox1.Clear()

        TextBox2.Clear()

        TextBox3.Clear()

        TextBox4.Clear()

        TextBox5.Clear()

 

    End Sub

End Class