The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

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;

 

 

How To Solve “GatherAllFilesToPublish” Error?

Issue:

I’m getting an error “GatherAllFilesToPublish”  when tried to publish my code. What’s happening to my csproj file?

---------------------------
Publish failed
---------------------------
Publish has encountered an error.

Build failed. Check the Output window for more details.


A diagnostic log has been written to the following location:

"C:\Users\username\AppData\Local\Temp\1\tmpC98.tmp"
---------------------------
OK   
---------------------------

Error MSB4057: The target "GatherAllFilesToPublish" does not exist in the project

 

Environment:

Windows 10, Visual Studio 2019, C# Project with Framework 4.0

Resolution:

This error is due to incorrect setting on project file (*.csproj). Please follow step-by-step instructions below.

Step-by-step instructions:-

  1. Open you project under Visual Studio
  2. Right click on project name and select “Unload Project”
  3. Right click on project name again and select “Edit *.csproj”
  4. Scroll down to the bottom of the file and you should see following line:
    <Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" />
  5. Replace above line with following two lines:
    <Import Project="$(VSToolsPath)\WebApplications\Microsoft.WebApplication.targets" Condition="'$(VSToolsPath)' != ''" />
    
    <Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" Condition="false" />

  6. Save the file and right click on project name to Reload the project
  7. Try publish again to see if it solves the issue

Troubleshoot:

If fail again, please verify by going through step 2 to 4 to see if two lines still there.