I use SSMS and the Import Data Wizard quite a bit.  Quickly importing data is a fairly common and anoften task that we all have to perform.  The wizard simply makes it more efficient given less time to perform the task.  Most of this data is, like mentioned, quick imports of data that will be used and dropped shortly after it has been used.

I’ve been running on Windows 8 Pro for some time now.  I have to say, I love it and can’t see going back to Windows 7 or anything else.  One thing I did run into on Windows 8 Pro and the import wizard was an error trying to import Excel 2007 so I could perform some data transforms.

I cannot say if the exact setup has something to do with the issue as I’ve read of the error on Windows 7 but, it was present with the following setup.

Windows 8 Pro, Office 2013, SQL Server 2012 and Tools – Importing Excel 2007 files with import wizard

The interesting part of this situation was, on the same setup I had already successfully imported Excel 2007 files. Recently I did install an update to Office though so I am assuming the cached assembly or some other cached factors played a part in the error.

The Error

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

When I received this error, the quickest way to check if I did indeed have the ACE.OLEDB.12.0 provider was to just the linked server providers list.  Below shows that both 15 and 12 were available.

Further linked server setup to the same Excel file proved the provider was functioning correctly.   This was leading to the import wizard possibly being the problem.  However, this is a key in troubleshooting that should be taken and assumptions can be made but research is the first stage in troubleshooting.

Fix

After a quick BOL search, I found this possible solution.

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

After installing the AccessDatabaseEngine.exe, I tried the import wizard again and received the following.

Now, typical windows computing and most computing entails caching into volatile memory in order to make application run faster.  Although it is possible to force things into cache, it often doesn’t happen for application that are up and running.  That being said, restarting SSMS was the task that needed to happen.  Once SSMS was simply closed and reopened, the import data wizard functioned correctly.

Summary

This is a possible fix for the ACE.OLEDB.12 unregistered error.  I have read some forum posts that had the same error and it occurred on Windows 7 as well so this very well might be a solution for you if you are on Windows 7 or Windows 8.