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:

#ObjectsDescription
1.ConnectionThis component is used to set up a connection with a data source.
2.CommandA command is a SQL statement or a stored procedure used to retrieve, insert, delete or modify data in a data source.
3.DataReaderData reader is used to retrieve data from a data source in a read-only and forward-only mode.
4.DataAdapterThis is integral to the working of ADO.Net since data is transferred to and from a database through a data adapter. It retrieves data from a database into a dataset and updates the database. When changes are made to the dataset, the changes in the database are actually done by the data adapter.
Objects and Descriptions of ADO.net Provider

How to Connect Access Database in VB.net

Let’s begin:

Time needed: 20 minutes

Steps How to Connect Access Database in VB.Net

  1. Step 1: Create an MS Access Database.

    Open an MS Access Database on your Computer and Create a Blank Database and Save it as “inventorydb.accdb”.

  2. Step 2: Create a Database Table.

    To create a table, follow the image below and save it as “tblitems”.
    Database Table tblitems

  3. Step 3: Populate the table.

    Add sample records in the table. follow the sample records in the image below.Sample records

  4. Step 4: Create a VB.Net Application.

    Open Visual Studio and Create a Visual Basic Application project and Save it as “connectvbaccess”.

  5. Step 5: Design the user interface.

    To design the form, you need to follow the image below.
    Form Design

  6. Step 6: Add New Data Source.

    On the menu, click data and select “Add new Data Source..”add new Datasource

  7. Step 7: Choose a Data Source Type

    Select Database and click Next.choose a data source type

  8. Step 7: Choose a Data Source

    Click new Connection then, Select the Microsoft AccessDatabase file and, Click Continue. Follow the image below.choos data source

  9. Step 9: Add Connection

    First, click the Browse Button then, Select “inventorydb.accdb”, Lastly, Click Open.
    add Connection

  10. Step 10: Test Connection

    To test the connection, click the “Test Connection” button, and finally, click the “OK” button at the side of the “Cancel” button.
    test connection

  11. Step 11: Copy the Connection String.

    Copy the connection string so that we can use this in our next step.
    connection string

  12. Step 12: Start Coding.

    In this final step, we will start adding functionality to our vb.net program by adding some functional codes.

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.

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

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

What is a Data provider?

The data provider is used to connect to a database. Executing commands and retrieving data, storing it in a Dataset, reading the retrieved data, and updating the database.

What is a Database Connection?

This component is used to set up a connection with a data source

What is Command?

A command is a SQL statement or a stored procedure used to retrieve, insert, delete or modify data in a data source.

What is DataAdapter?

This is integral to the working of ADO.Net since data is transferred to and from a database through a data adapter. It retrieves data from a database into a dataset and updates the database. When changes are made to the dataset, the changes in the database are actually done by the data adapter.

What is DataSet?

The dataset is an in-memory representation of data. It is a disconnected, cached set of records that are retrieved from a database. When a connection is established with the database, the data adapter creates a dataset and stores data in it.

What is DataTable?

Datatable consists of the DataRow and DataColumn objects. The DataTable objects are case-sensitive.

What is OledbConnection?

OleDbConnection is designed for connecting to a wide range of databases, like Microsoft Access and Oracle.

What is SqlConnection?

SqlConnection is designed for connecting to Microsoft SQL Server.

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


6 thoughts on “How to Connect Access Database in VB.Net”

Leave a Comment