System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
This error gets reported every now and then and this is my try to explain what is going on.
The scenario: You have written a .Net app that connects and interacts with Access 2007.
When you move it to an x64 machine, it stops working with the following error exception:
System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
Ok, so first, let’s do this step by step.
.1 Create a new accdb file. So open up Access 2007 and create a new database in C:\Temp, call it Access2007.accdb
.2 Create some columns and rows like this:
ID FirstName LastName
2 Mike Spike
3 Steve Stevenson
4 John Johnson
.3 Save the table as AccessTable
.4 Start Visual Studio (on 64 bit machine) and create a new C# console application and replace the code in the main method with this:
static void Main(string[] args)
{
string directory = @"C:\Temp";
string file = "Access2007.accdb";
string sql = "SELECT ID, FirstName, LastName FROM AccessTable";
string cs = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}\{1};Persist Security Info=False;", directory, file);
try
{
using(OleDbConnection connection = new OleDbConnection(cs))
{
connection.Open();
using(OleDbCommand cmd = new OleDbCommand(sql, connection))
{
OleDbDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
Console.WriteLine("ID:{0}, FirstName: {1}, LastName: {2}", rdr[0], rdr[1], rdr[2]);
}
}
connection.Close();
}
}
catch(Exception ex)
{
Console.WriteLine(ex);
}
}
.5 Run it and you should get the exception above.
So what is going on?
Well, there is currently no 64bit version of the Access driver, only a 32bit version.
To make a long story short, by default your .Net project will be compiled with the anycpu platform switch. This means that when Windows have examined the .exe header
it will (on a 64 bit machine) decide that this is a 64bit process. Of course, if you were running on a 32bit machine, Windows would decide that this is a 32 bit process.
But when running as a 64bit process, the 32bit driver will not be found.
So, since there is no 64 bit version of the Access driver, we need to run the .exe as a 32 bit (Wow64 application) process on the 64 bit machine.
This is easy to do.
.1 Right click your project and select Properties.
.2 Select Build and select x86 in the Platform Target dropdown.
.3 Save, Rebuild and run you project, you should now get the rows for the table.
"C# Language Reference - /platform (Specify Output Platform) (C# Compiler Options)"