Connecting and editing mdb files

Sometimes old data are in mdb files. If you have not Microsoft Office Access, this solution could be useful for you. This sample demostrates how to connect and edit mdb files data through the datagridview (cell value event) events.

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

namespace mdbViewer
{
    public partial class Form1 : Form
    {
        OleDbConnectionStringBuilder cnsBiulder = new OleDbConnectionStringBuilder();
        BindingSource bnd = new BindingSource();
        OleDbConnection myConn = new OleDbConnection();
        OleDbCommand myCmd = new OleDbCommand();

        public Form1()
        {
            InitializeComponent();
            bindingNavigator1.BindingSource = bnd;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = Application.ProductName;
        }

        private void bttOpenDb_Click(object sender, EventArgs e)
        {
            OpenFileDialog fopen = new OpenFileDialog();
            fopen.Filter = "mdb files|*.mdb";
            if (fopen.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                cnsBiulder.Provider = "Microsoft.Jet.OLEDB.4.0";
                cnsBiulder.DataSource = fopen.FileName;
                this.Text = Application.ProductName + ": " + System.IO.Path.GetFileName(fopen.FileName);

                DataTable dt =new DataTable();
                OleDbConnection cnn = new OleDbConnection();
                OleDbCommand cmd = new OleDbCommand();
                cnn.ConnectionString = cnsBiulder.ConnectionString;
                cnn.Open();
                fillComboWithTableNames(cnn);
            }
        }

        private DataTable getTableData(OleDbCommand cmd) 
        {
            DataTable dt = new DataTable();
            OleDbDataAdapter ad = new OleDbDataAdapter();
            ad.SelectCommand = cmd;

                try
                {
                    ad.Fill(dt);
                    return dt;
                }
                catch (Exception)
                {
                    return null;
                }  
        }


        private DataTable fillComboWithTableNames(OleDbConnection con)
        {   
            DataTable dt=con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] {null, null, null, "TABLE"});

            foreach (DataRow row in dt.Rows)
            {
                toolStripComboBox1.Items.Add(row["TABLE_NAME"]);
            }
        return dt;
        }


        private void selectTableName(object sender, System.EventArgs e)
        {
            OleDbConnection cnn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            cnn.ConnectionString = cnsBiulder.ConnectionString;
            cnn.Open();
            cmd.CommandText = "SELECT * FROM " + toolStripComboBox1.SelectedItem;
            cmd.Connection = cnn;
            bnd.Position = 0;
            bnd.DataSource = getTableData(cmd);
            dataGridView1.DataSource = bnd;
            //label1.DataBindings.Add(new Binding("Text", bnd, dataGridView1.Columns[1].Name));
         }


        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            string updatedField = dataGridView1.Columns[e.ColumnIndex].Name;
            string updatedValue = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            string updatedTableName = toolStripComboBox1.SelectedItem.ToString();
            string where = " WHERE " + dataGridView1.Columns[e.ColumnIndex-1].Name + "='" + dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex-1].Value.ToString() + "'";
            string sqlCommand = "UPDATE " + updatedTableName + " SET " + updatedField + "=" + updatedValue + " " + where;
            SqlCommander(sqlCommand);
            using (OleDbConnection cnn = new OleDbConnection(cnsBiulder.ConnectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = cnn;
                    cmd.CommandText = "SELECT * FROM " + toolStripComboBox1.SelectedItem.ToString();
                    dataGridView1.DataSource = getTableData(cmd);
                }
            }
        }

        void SqlCommander(string sqlCommand)
        {
            using (OleDbConnection cnn = new OleDbConnection(cnsBiulder.ConnectionString))
            {
                cnn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sqlCommand, cnn))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        MessageBox.Show("Sql erre: " + sqlCommand + Environment.NewLine + "Error desc: " + ex.Message, "Sql error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                }
            }
        }      
    }
}