Business Intelligence

How to Install both 32-bit and 64-bit Microsoft.ACE.OLEDB.12.0 Providers…

There are probably no one who uses Excel as a Datasource and hadn’t been haunted by that famous error:

The โ€˜Microsoft.ACE.OLEDB.12.0โ€™ provider is not registered on the local machine.

or better to say: this, when you try to install that provider:

You cannnot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Offce products installed. If you nant to install 32-bit Microsoft Access Database Engine 2016, you veill first need to remove the 64-bit installaton of Offce products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016:
Microsoft Access database engine 2010 (English), Office 16 Click-to-Run Extensibility Component

and this is how that haunted message looks like:

Welcome to the club ๐Ÿ™‚

It is being suggested in that message that you should either have a fight with your system admin or you have plenty of time to un- and re-install complete Office in order to have the correct/desired version of the provider.

If you are also developing SSIS Packages, you are also probably aware that Visual Studio is an 32 bit application and you will loose the Design-Time support if you decide to continue with the 64-bit version of the Office.

I mean, until today….

Today you will learn how to install both versions in the same machine! And indeed a very easy trick.

(drum roll please!!)

  1. Open Powershell/Command Prompt
  2. Locate the Access Database Engine.exe that you want to install
  3. add “/quiet” switch at the end and execute
  4. Tadaaaa! That’s it.

Reason is really funny. Error message that you’ve received is caused by the setup bootstrap package which checks if another version of the same product is installed or not. However that “/quiet” swtich prevents it to bark back at you.

Now I have a design time support of Excel files in SSIS and also I can enjoy the benefits of 64-bit Version as well. So… But…

– Is it supported (to have both versions on same machine)?
– I dunnow!
– Does it work anyway?
– Awww f. yes!


Note that: /quiet switch only works for versions Office 2016 and later . For previous versions you might have a look at /passive switch and a registry edit.

Other Tips

Tip: Increase the Font Size of SQL Management Studio

Absolutely stupid tip but it is real. With the increase of high-resolution graphic cards (and the age of course) everything is getting smaller and sometimes it is more healthier and better for our old eyes to read results in bigger fonts. On the other hand it is especially required when you are doing a presentation or sharing your screen with someone and it is like an ant party that happens in this grid ๐Ÿ™‚

Recently I’ve experienced such a thing and desired to increase the font size of the SSMS. I am used to zooming in and out by using ctrl+mouse scroll but realised that this technique only applies to the query part of the screen in SSMS.

You can also set the zoom level from the combo on the bottom-left side of the query screen.

But it is not possible to increase the size of the results grid from anywhere in this screen and ctrl+mouse scroll also doesn’t work. The remedy is hidden under Tools => Options ๐Ÿ™‚

Just follow menu Tools => Options and in options dialog expand Environment => Font and Colors group. In “Show settings for:” section, select “Grid Results” and adjust the text and font size as desired. (But please no Comic Sans :))

Important: You should have to restart SSMS before this change takes effect.

One more last-minute tip: You can change the whole font of the User Interface. Just follow this:

For more tips like above, you can visit https://www.ssmstipsandtricks.com/.