Connect Access 2007-2010 Database In C#

Friends,

In this post, we will see how can we connect Access 2007-2010 database in C#. As we know Microsoft introduced new Access format from Access 2007 onwards (.accdb extension), we cannot use the default JET.OLEDB provider that we used to use before.

With the new database, Microsoft also launched a new provider namely ACE.OLEDB to connect with the database. Let’s see a quick sample below –

        private DataTable GetStudents()
        {
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Schools.accdb;Persist Security Info=False;"))
            {
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM Students", con);
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                    dt.Load(reader);

                con.Close();
            }
            return dt;
        }

The above code connects to an Access 2010 database using the new connection provider with Students table in Schools database and retrieve all records. The most important point to note in the code is the Connection Provider  – “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Schools.accdb;Persist Security Info=False;”

Hope you like this post. Keep learning & sharing! Cheers!