Using MSAccess with VB.NET

Data Access using MSAccess

To work with Microsoft Access we use the OleDb data Provider.

Sample Code

Create a database named Emp in Microsoft Access in the C: drive of your machine. In the Emp database create a table, Table1 with EmpNo, EName and Department as columns, insert some values in the table and close it. Open Visual Studio .NET, on a new form drag three TextBoxes and a Button. The following code will assume that TextBox1 is for EmpNo, TextBox2 is for EName and TextBox3 is for Department. Our intention is to retrieve data from Table1 in the Emp Database and display the values in these TextBoxes without binding when the Button is clicked.

Code for retrieving records

Imports System.Data.OleDb
Public Class Form1 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e as _
System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;_
Data Source=C:\emp.mdb;")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
' loading data into TextBoxes by column index
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub
End Class

When you run the code and click the Button, records from Table1 of the Emp database will be displayed in the TextBoxes.

Retrieving records with a Console Application

Imports System.Data.OleDb
Imports System.Console
Module Module1

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Sub Main()
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;_
Persist Security Info=False")
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
WriteLine(dr(0))
WriteLine(dr(1))
WriteLine(dr(2))
'writing to console
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub

End Module

Code for Inserting a Record

Imports System.Data.OleDb
Public Class Form2 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button2.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
cn.Open()
str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" &_
TextBox3.Text & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch
End Try
cn.Close()
End Sub
End Class