MS Access Development–ADO.NET Online Mode

📘
Phonebook Project
Using VB.NET and MS Access

This example demonstrates a fully functional Phonebook application using VB.NET [I'm using Visual Basic 2017, if you wish! You can use any version] (Framework 4.8) with Microsoft Access in Connected Mode. It includes:

Access Database: PhoneB.accdb with table Phone1
Fields: Fname, Lname, Country, City, Phone, Mobile, Lemail (Short Text), Lphoto (OLE Object)
Form Controls: TextBoxes for each field (e.g., TxtFname), PictureBox (Pphoto), Buttons (New/Save, Edit, Delete, Search), and a DataGridView (DG1)

ms access development using adonet connected mode

💡 Step 1: Define Module-Level Variables

Imports System.Data.OleDb

This line of code import necessary namespaces: System.Data.OleDb is used for ADO.NET operations with Access databases.

Public Class MainForm
    Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PhoneB.accdb"
    Dim conn As New OleDbConnection(connStr)
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim sql As String

This block of code represents globally declared objects: connStr is the connection string to Access database. conn, cmd, dr are the main ADO.NET objects. CDL is used for selecting image files from disk.

💡 Step 2: Save New Record

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
    Try
        sql = "INSERT INTO Phone1 (Fname, Lname, Country, City, Phone, Mobile, Lemail) VALUES (?,?,?,?,?,?,?)"
        cmd = New OleDbCommand(sql, conn)
        With cmd.Parameters
            .AddWithValue("?", TxtFname.Text)
            .AddWithValue("?", TxtLname.Text)
            .AddWithValue("?", TxtCountry.Text)
            .AddWithValue("?", TxtCity.Text)
            .AddWithValue("?", TxtPhone.Text)
            .AddWithValue("?", TxtMobile.Text)
            .AddWithValue("?", TxtLemail.Text)
        End With
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        MsgBox("Saved Successfully.")
    Catch ex As Exception
        MsgBox("Error: " & ex.Message)
    End Try
End Sub

Code to Validate fields, insert a new record, and convert image to bytes before saving.

💡 Step 3: Load Data into DataGridView

Private Sub LoadData()
    Try
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter("SELECT * FROM Phone1", conn)
        da.Fill(dt)
        DG1.DataSource = dt
    Catch ex As Exception
        MsgBox("Error: " & ex.Message)
    End Try
End Sub

This code Loads all records from the database into the DataGridView and applies formatting.

💡 Step 4: Edit Selected Record

Private Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
    sql = "UPDATE Phone1 SET Lname=?, Country=?, City=?, Phone=?, Mobile=?, Lemail=? WHERE Fname=?"
    cmd = New OleDbCommand(sql, conn)
    With cmd.Parameters
        .AddWithValue("?", TxtLname.Text)
        .AddWithValue("?", TxtCountry.Text)
        .AddWithValue("?", TxtCity.Text)
        .AddWithValue("?", TxtPhone.Text)
        .AddWithValue("?", TxtMobile.Text)
        .AddWithValue("?", TxtLemail.Text)
        .AddWithValue("?", TxtFname.Text)
    End With
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()
    MsgBox("Updated Successfully.")
End Sub

This code Updates a record in the database using the value of Fname as a key.

💡 Step 5: Delete Record

Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
    sql = "DELETE FROM Phone1 WHERE Fname=?"
    cmd = New OleDbCommand(sql, conn)
    cmd.Parameters.AddWithValue("?", TxtFname.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()
    MsgBox("Deleted Successfully.")
End Sub

This code Deletes a record based on the Fname.

💡 Step 6: Search by First Name

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
    sql = "SELECT * FROM Phone1 WHERE Fname=?"
    cmd = New OleDbCommand(sql, conn)
    cmd.Parameters.AddWithValue("?", TxtFname.Text)
    conn.Open()
    dr = cmd.ExecuteReader()
    If dr.Read() Then
        TxtLname.Text = dr("Lname").ToString()
        TxtCountry.Text = dr("Country").ToString()
        TxtCity.Text = dr("City").ToString()
        TxtPhone.Text = dr("Phone").ToString()
        TxtMobile.Text = dr("Mobile").ToString()
        TxtLemail.Text = dr("Lemail").ToString()
    Else
        MsgBox("Record not found.")
    End If
    dr.Close()
    conn.Close()
End Sub

This code Searches the database by Fname and populates the form fields including the photo.

🔚 Final Notes

OLE Object handling for photos can be added using FileStream and byte arrays.
Always wrap database operations in Try/Catch blocks to manage exceptions.

📥 Download Project (OneDrive)

📁 Download from MediaFire

♥ Here are some online Visual Basic lessons and courses:

Sitemap for this BLOG

Evry1falls

Mastering VB.NET - ADO.NET: Expert Tutorials, Code Solutions & Database Insights

Looking for MS Access Developer❓❓

Application developer