Problems Importing Access and Excel 2010 into SQL Server 2012?

Edit 10 Dec 2015: This blog post was written to solve a problem with SQL Server import drivers, however the process of addressing the issue should also work (in reverse) for people that are running 32 bit Office and want to install 64 bit Power BI Desktop.  If this is you, read on and you will see the process to fix the problem.

Since becoming a PowerPivot Professional, I have broadened my skill base to include the use of SQL Server.  I very quickly found that MS Access has a 2GB file size limit and hence I installed SQL Server on my PC.  It was a bit scary to start with, but once you get it installed and get started it is actually a fabulous way to go.  I would never go back now.  Anyway, I have had A LOT of issues with file compatibility when importing Access and Excel 2010 files into SQL Server.  It took me months to find a solution, mainly because I actually didn’t understand what the problem was – until now.

First an explanation of the problem – Office 64bit

Any serious PowerPivot user will already know that you really need Office 64 bit version.  PowerPivot will work with 500k – 1m rows of data in the 32 bit version (more or less, depending on your data) but if you want to get serious (ie many millions of rows of data), then you need to move to 64 bit.  The reality is that Excel with PowerPivot is THE ONLY Office program that needs 64 bit.  Now the problem is that there are lots of plugins and software that are compiled to work only with the 32 bit version.  That is why IT departments hate 64 Bit Office so much.

So the problem is as follows:

1. SQL Server Management Studio allows you to import Office 2010 file formats *.xlsx and *.accdb when you install the correct driver.
2. The driver you need to install to work with the SSMS import wizard is the 32 bit version, because the import wizard is compiled for the 32 bit version only.
3. When you download  AccessDatabaseEngine.exe 32 bit version and go to install it, you get an error message saying
   “You cannot install the 32 bit version of Access Database Engine 2010 because you have 64 bit Office products installed”.

1. So you then download AccessDatabaseEngine.exe 64 bit version and that will install just fine – trouble is that you can’t use it with the import wizard because it only supports the 32 bit version.

Catch-22!  Well until now.

So after learning about the ACTUAL problem on this forum, and doing some other R&D on the Net, I have discovered a work around to the problem.

The Solution

1. Download AccessDatabaseEngine.exe 32 bit version here
2. Copy the file to a location on your PC that is easy to access from the command line.  I put mine in C: root folder
3. Click the windows button and type run and press enter
4. type c:AccessDatabaseEngine.exe /passive into the command line box and click “OK”.  Of course you should use the correct path to your file.
5. The files will install successfully in the background while suppressing errors.

Note:  When you launch SSMS and try to import an Access DB with format *.accdb you need to select “Microsoft Access Database Engine” as the data source.  By default, the file extension is *.mdb and there is no *.accdb, however you can use the *.* extension and browse to find your *.accdb file.  Once you have done this, the rest of the process should work just fine.

I hope this explanation and solution helps others to solve this problem.