Issue:
I have a MVC app that read Microsoft Access file (mdb) and update the file with latest data from SQL. Everything work on local machine until when I published to Web Server, I get following error:
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.
Why I’m getting the error?
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, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)at System.Data.OleDb.OleDbConnection.Open()at AppName.Web.Areas.Admin.Controllers.AdminController.Offline(Int32 AssessmentId) in A:\Temp\AppName.Web\Areas\Admin\Controllers\AdminController.cs:line 3258at lambda_method(Closure , ControllerBase , Object[] )at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)
Environment:
MVC, IIS, SQL, MDB Access File
Resolution:
Since The ‘Microsoft.ACE.OLEDB.12.0’ provider is not installed on web server, you won’t be able to use this dll. Instead, you should be using “Microsoft.Jet.OLEDB.4.0” provider instead. This is already preinstalled on IIS Web Server.
Step-by-step instructions:-
Change from: Microsoft.ACE.OLEDB.4.0
OleDbConnection myConn; myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + targetPath +"\\"+ myFileName); myConn.Open(); OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = myConn;
To this: Microsoft.Jet.OLEDB.4.0
OleDbConnection myConn; myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetPath +"\\"+ myFileName); myConn.Open(); OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = myConn;