The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine while data export to MS ACCESS

Question

I am beginner in using Microsft.ACE.OLEDB 12.0. I create a Winforms application VS 2010.

And create a function for export data grid data to MS Access file. I using Microsoft Oledb

for export the data grid data to MS Access file.But i get this error when i try to export

the

data to ms access.

Please see the below image.

I also refer the Microsoft.Office 12.0 Object Library in my application.

Batch Build Configuration

Solution 1

The reference to the Access Interop bits has nothing to do with your exception and Access Interop is not necessary to use the classes in the System.Data.OleDb.

The problem arises when you have your application compiled for AnyCPU Platform, you are running on a 64bit system and the installed ADO.NET provider (Microsoft ACE.OLEDB.12.0) is the 32bit version.

When using the AnyCpu target Platform your code will be executed as 64bit code on 64bit systems and as 32bit code on 32bit systems. An application executed as 64bit cannot use 32bit drivers (and viceversa). Now add to the mix the fact that Microsoft.ACE.OLEDB.12.0 has two different versions. One for 64bit and and one for 32bit and they cannot be installed together on the same machine.

The simplest workaround is to change the Target Platform of your application through Visual Studio menu

 BUILD -> Configuration Manager -> Active Solution Platform -> x86

If the x86 option is not already there, then select NEW, name it x86, Copy Settings from AnyCPU and check Create new project platforms

If you think that using a 32bit app on a 64bit Operating System is a loss of performance or something to be avoided then think twice and read this reference where the PRO and CONS of AnyCpu are critically examined. If you don't have a specific reason to use AnyCpu it is better to stay with x86.

Of course, another option is to deinstall the 32bit version and install the 64bit version of ACE from here and then run you application as AnyCpu on 64bit systems. But this could be a nightmare for your deployment scenarios. What if Microsoft Office 32bit version is installed on your x64 target machine? Office installs its bit compatible version of ACE and, as said, it is not possible to have 32bit and 64bit of ACE installed on the same machine.
Now you should also ask your customer to reinstall Office as 64bit to keep your 64bit app happy.

UPDATE

AnyCPU Prefer 32bit mode

What AnyCPU Really Means As Of .NET 4.5 and Visual Studio 11

The trouble with Any CPU–Prefer 32 bit–BadImageFormatException

Solution 2

Change in its App pool 'advanced settings' to allow to run 32-bit programs... that did it for me.

Solution 3

I have similar issue when we are reading Excel file.

History of the problem:

We recently migrated our application from 32-bit to 64-bit because of the memory requirement. For that we migrated our windows 7 from 32-bit to 64-bit. But still we installed 32-bit office on our machines.

because, of this we had this issue while importing Excel data into application.

Solution,

I downloaded 64-bit version of the http://www.microsoft.com/en-us/download/details.aspx?id=13255 and installed with argument as,

AccessDatabaseEngine_x64.exe /passive

Without any code change my issue get resolved.

Note:

On 64-bit OS and 64-bit office, my functionality was working fine without this fix. This fix is only required while our application is 64-bit running on 64-bit OS which is having 32-bit office installed on it.

Solution 4

Base way:

Application : MVC C#, For Win Server 64 bit:

1. Remove all office 32 bit installed.

2. Download link: https://www.microsoft.com/en-us/download/details.aspx?id=13255

3. select download 64 bit and then install it to server(If you can't setup program. Please re-check 1 point).

4. retry access your application again.enter image description here

Solution 5

for me installing Microsoft Access Database Engine 2010 Redistributable fixed the problem, the 32 bit version.

View more solutions

Comments

[Ragesh S]

Thank you for your valueable reply. My Operating system is Windows 7 64 bit. Please see my edited question i add a image in the Batch Build configuration

[Steve]

No Batch Build, use Configuration Manager and set the Active Solution Platform as x86

[Ragesh S]

But Is it work in both platform I mean there is a chance to use 32 bit and 64 bit OS.

[Steve]

If you compile as x86 it works on both Platforms, provided you have the 32bit version of ACE installed also on x64, it is a mess I know, but this is a problem that MS has yet to resolve

[Ragesh S]

Hi sir, I change my application Platform target to x86. Application Properties -> Build -> Platform target = x86. Now it's works Fine. Thank you for your valuable reply.

[Steve]

I could give you this reference, Microsoft itself says that if you don't have a specific reason to use AnyCPU it is better to stick with x86

[Mido]

This has by far been the best explanation of this issue. Thanks Steve.

[Amen Jlili]

a million thanks for the solution! especially for the /passive argument!

[gumuruh]

advanced settings... from where is that?

[gumuruh]

for me... as long as the target client is using office x64bit, and my project is developed under x86bit,... the error will still exist unless i installed the additional Redistributable Office Engine version 2007 as a replacement for the 32bit usage.

[Mike]

Microsoft Access Database Engine 2016 Redistributable is now available here microsoft.com/en-us/download/details.aspx?id=54920 for Excel 2019, which also solved the problem for me.