How to open connection with Microsoft Access database in C#

Question

I'm using Microsoft Access to create my database. Here's my code:

static string Constr = "Provider=Microsoft.Jet.OLEDB.4.0;" 
                     + "Data Source = MyData.accdb";
OleDbConnection Conn = new OleDbConnection(Constr);
DataSet DataSet1 = new DataSet();
string SQLstr = "Select * from Tabel";
OleDbDataAdapter DataAdapter1;
Conn.Open();

I'm getting this exception:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Unrecognized database format

Answer 1

You are trying to open an Access database created with Access 2007/2010 (accdb) with an OleDb provider that can understand only MDB files created with Access 2003

The provider to use is Microsoft.ACE.OleDB.12.0 that should be already installed with the latest version of Office.

In alternative you could download the appropriate bits from the Microsoft Access Database Engine
Just pay attention to download the version appropriate for the platform (x86 or x64) you use.

And that's the connection string to use

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyData.accdb";

NOTE: Keep in mind that if you have Office installed, the corresponding provider has the same bitness of Office, so with Office 64bit comes the ACE in 64bit version and the same for 32bit. This scenario poses a very difficult problem in deployment of your application. You need to have you application compiled with the same bitness or you will not be able to use the provider installed. (Or disinstall the incompatible Office version and reinstall the compatible one. Of course if you should be able to persuade your customer that Office is the incompatible app :-)