How to Connect Access Database in VB.Net

Database Access in VB.net

Applications talk to a database in two ways: first, to get the data stored there and show it in a way that’s easy to understand, and second, to update the database by adding, Updating, or removing data.

Microsoft ActiveX Data Objects.Net (ADO.Net) is a model that is part of the .Net framework.

It is used by .Net applications to get, access, and change data.

Data Provider

A data provider is used to connect to a database, run commands, retrieve data, store it in a dataset, read the data that was retrieved, and update the database.

The following four things make up the ADO.Net data provider:

How to Connect Access Database in VB.net

Let’s begin:

Code To Connect Access Database in VB.Net

Double the “Form1” and add the following code under “Public Class Form1”.

Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\joken\Documents\inventorydb.accdb")

The Code above started with a Declaration of Variable name “con” with an Ole Object Type OledbConnection.

Inside OledbConnection, we pasted the connection string we copied from the “Step 11” instructions.

Test the Connection of Access database in VB.Net

To test the connection between the MS access database and VB.Net, Double click form1 and add the following code under “Form1_Load” events.

         Try
            con.Open()

            If con.State = ConnectionState.Open Then
                MsgBox("Connected")
            Else
                MsgBox("Not Connected!")

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()

        End Try

• We use try-catch to the exceptions that may occur during runtime.

• open the connection

• check using if statement if the connection is open

• ‘Display a message box if successfully connected or Not

• close the connection

Press “F5” to run the Project.

When you run the project it will give you this message.

How to Load Record from Access Database to Datagridview In VB.Net

In this section, we will learn how to load a record from the Access database to Datagridview using vb.net. To start with, double-click the “Load record” button and add the following code.

        Try
            Dim sql As String
            Dim cmd As New OleDb.OleDbCommand
            Dim dt As New DataTable
            Dim da As New OleDb.OleDbDataAdapter
            con.Open()
            sql = "Select * from tblitems"
            cmd.Connection = con
            cmd.CommandText = sql
            da.SelectCommand = cmd

            da.Fill(dt)

            DataGridView1.DataSource = dt
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()

        End Try

After adding the code, you may press F5 or click the Start debugging button to test the code. The output should look like as shown below.

Save Record in Access Database using VB.net

In this section, we learn how to save the record in the access database using vb.net. To do this, double-click the “Save Item” button and add the following code.

        Try
            Dim sql As String
            Dim cmd As New OleDb.OleDbCommand
            con.Open()
            sql = "INSERT INTO tblitems (ITEMNAME,ITEMDESCRIPTION,QTY,PRICE) values ('" & txtitemname.Text & "', '" & txtdescription.Text & "'," & Val(txtqty.Text) & "," & Val(txtprice.Text) & ");"
            cmd.Connection = con
            cmd.CommandText = sql
            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("New record has been inserted successfully!")
            Else
                MsgBox("No record has been inserted successfully!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
           
        End Try

Code explanation:

• We start the code by adding a try-catch.

• declare variable “sql” as a string this will hold the INSERT STATEMENT.

• Declare variable “cmd” as oledbCommand it represents an SQL Statement or Store procedure to execute against a data source.

• next, we open the connection.

• Then, we assign a query command to the “sql” variable.

• Execute the Command.

• Then we assign the result of “ExecuteNonQuery” to the “i” variable.

• Next, we check if the value of the variable “i” is greater than 0.

• we use the if condition statement if the result is true then will display a message box “New record has been inserted successfully!”.

• else if the result is false, the display is “No record has been inserted successfully!”

At this time, you can now press “F5” to test the code.

Updating of Records from Access Database In VB.Net

In this section, we will learn how to update records from an access database using vb.net. In order for us to proceed in updating the record, we will add first a code to pass value from datagridview to textboxes.

To start with, go back to form design and double-click the datagridview. And Change the Event to “CellClick” from “CellContentClick”.

It means that every time the user clicks the selected data in the Datagrid view, the value will automatically pass to the text boxes.

So here’s the following code.

        Me.Text = DataGridView1.CurrentRow.Cells(0).Value
        txtitemname.Text = DataGridView1.CurrentRow.Cells(1).Value
        txtdescription.Text = DataGridView1.CurrentRow.Cells(2).Value
        txtqty.Text = DataGridView1.CurrentRow.Cells(3).Value
        txtprice.Text = DataGridView1.CurrentRow.Cells(4).Value

And here’s the Following code for Updating the record from the access database using vb.net.

         Try
            Dim sql As String
            Dim cmd As New OleDb.OleDbCommand
            con.Open()
            sql = "UPDATE tblitems SET ITEMNAME='" & txtitemname.Text & "', ITEMDESCRIPTION='" & txtdescription.Text & "', " & _
             " QTY=" & Val(txtqty.Text) & ", PRICE=" & Val(txtprice.Text) & " WHERE ID=" & Val(Me.Text) & ""
            cmd.Connection = con
            cmd.CommandText = sql

            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("Record has been UPDATED successfully!")

            Else
                MsgBox("No record has been UPDATED!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
          
        End Try

The code we use for updating the record from the ms access database using vb.net is almost similar to the code above for inserting a new record to the ms access database in vb.net.

Only the Query is different.

Deleting of Records from Access Database In VB.Net

For deleting records from the access database in vb.net, we will still use the same code in inserting and updating the record from access using vb.net.

Go back to Form design and double-click the “Delete Item” button. Then add the following code.

         Try
            Dim sql As String
            Dim cmd As New OleDb.OleDbCommand
            con.Open()
            sql = "Delete * from tblitems WHERE ID=" & Val(Me.Text) & ""
            cmd.Connection = con
            cmd.CommandText = sql

            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("Record has been deleted successfully!")

            Else
                MsgBox("No record has been deleted!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            
        End Try

After this process, you can now run the program to test if all the codes in this tutorial are working well.

Bonus Tips

Watch the full video tutorial about How to Connect to an Access Database in VB.NET until the end to see the bonus techniques and apply them to your project.

After you complete this tutorial, you can proceed on how to master VB.Net and MySQL Database in 30 minutes.

Download the vb.net project full Source code Below.

Summary

In summary, we have discussed how to connect the access database in vb.net. Then we add code for loading of records from the access database to datagridview using vb.net.

We learned also how to insert, update, and delete records from the access database using vb.net.

Frequently Asked Questions

If you have any questions or suggestions about how to connect to access the database in vb.net, please leave a comment below.

Other Articles Readers might read:

• How to Load Data From MySQL Database to Table Element Using Java
• How to Use a Module in AutoSuggest and AutoAppend in VB.Net and MySQL Database

PREVIOUS

Regular Expression

NEXT

Excel Sheet