How to Connect to an Access Database in C#

Muhammad Zeeshan Feb 02, 2024
How to Connect to an Access Database in C#

This article will discuss connecting to an Access database in C#.

Microsoft Access

Microsoft Access is a data management program that allows you to save data for future reference, reporting, and analysis. Unlike Microsoft Excel or other spreadsheet tools, Microsoft Access enables you to analyze vast volumes of data and efficiently handle related data.

Connect to an Access Database in C#

We can connect to an Access database in C# by following the steps below.

  • First, open Microsoft Access and select a Blank Desktop Database. Name the database, and then click Create.

  • Create a table in the database, and name it. We’ll call it EmployeeInfo, with four columns: Eid, Ename, Edept, and Eaddress.

  • Now, launch Microsoft Visual Studio and create a new Windows Form Application. In Solution Explorer, drag and drop the database file from Documents to the Project Directory folder generated using Microsoft Access.

  • Create a form design like the following:

    Create an Employee Info Form Design

  • Double click on the Submit Data Button, When you double-click the 1 button, an event will be created.

  • Now, add the following libraries for connectivity:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
  • Generate a connection string by going to the Tools and selecting Connect to the database from the list, then browse the database from the Project Directory.

    Add a Connection to the Database

  • Select Advanced and then Provider from the list. Copy the text as your connection string.

  • Now, create a connection string and assign it to a static string typed variable constr like the following:

    static string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application.StartupPath +
                           "/employeeinfo.mdb";
    
  • Initialize an OleDbConnection type variable dbcon to make a connection and pass connection string constr as a parameter:

    OleDbConnection dbcon = new OleDbConnection(constr);
    
  • Finally, add these lines of code to input employee information such as employee name, department, and address.

    OleDbCommand cmd = dbcon.CreateCommand();
    dbcon.Open();
    cmd.CommandText = "Insert into EmployeeInfo (Ename, Edept,Eaddress)Values('" + txtEmpname.Text +
                      "','" + txtEmpdept.Text + "','" + txtEmpaddress.Text + "')";
    cmd.Connection = dbcon;
    cmd.ExecuteNonQuery();
    MessageBox.Show("Data Inserted Successfully");
    dbcon.Close();
    

Example Source Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication1 {
  public partial class Form1 : Form {
    static string constr =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application.StartupPath +
        "/employeeinfo.mdb";
    OleDbConnection dbcon = new OleDbConnection(constr);

    public Form1() {
      InitializeComponent();
    }

    private void button1_Click_1(object sender, EventArgs e) {
      OleDbCommand cmd = dbcon.CreateCommand();
      dbcon.Open();
      cmd.CommandText = "Insert into EmployeeInfo (Ename, Edept,Eaddress.)Values('" +
                        txtEmpname.Text + "','" + txtEmpdept.Text + "','" + txtEmpaddress.Text +
                        "')";
      cmd.Connection = dbcon;
      cmd.ExecuteNonQuery();
      MessageBox.Show("Data Inserted", "Congrats");
      dbcon.Close();
    }
  }
}
Muhammad Zeeshan avatar Muhammad Zeeshan avatar

I have been working as a Flutter app developer for a year now. Firebase and SQLite have been crucial in the development of my android apps. I have experience with C#, Windows Form Based C#, C, Java, PHP on WampServer, and HTML/CSS on MYSQL, and I have authored articles on their theory and issue solving. I'm a senior in an undergraduate program for a bachelor's degree in Information Technology.

LinkedIn

Related Article - Csharp Database