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:

  

• 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.

  

• 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();
    }
  }
}