Read (Lookup) records from Access Database - Visual Basic .NET

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This tutorial will show you how to connect Visual Basic Form to Access Database and retrieve records. For example: you have Access database that has Items table:

read access table in visual basic .net

Also I have a Form in Visual Basic 2010:

visual basic select access table

The user will enter a code then click on Find and the records will appear in the description, cost and price textboxes:

search records in visual basic .net

In this example I use Microsoft Access 2010, and Visual Basic 2010.

Microsoft Access 2010 table:

microsoft access table

Visual Basic 2010:

Add four textboxes, four labels and a button:

search access records in visual basic

I named the textboxes as follows:

CodeText
DescriptionText
CostText
PriceText
and I named the button: FindButton

Go to code page, and the following code before (Public Class Form1) line

Imports System.Data.OleDb

Then add the following declarations below (Public Class Form1)

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Public myConnection As OleDbConnection = New OleDbConnection
    Public dr As OleDbDataReader

Add the following form load event code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        dataFile = "C:\Users\Jimmy\Desktop\Products.accdb" ' Change it to your Access Database location
        connString = provider & dataFile
        myConnection.ConnectionString = connString
End Sub

Now add the following FindButton click event code:

Private Sub FindButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindButton.Click
        myConnection.Open()
        DescriptionText.Clear()
        CostText.Clear()
        PriceText.Clear()
        Dim str As String
        str = "SELECT * FROM Items WHERE (Code = '" & CodeText.Text & "')"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
  dr = cmd.ExecuteReader
        While dr.Read()
            DescriptionText.Text = dr("Description").ToString
            CostText.Text = dr("Cost").ToString
            PriceText.Text = dr("Price").ToString
        End While
        myConnection.Close()
    End Sub

Run the program and enter a code to lookup then click on Find button.


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.