Microsoft.ACE.OLEDB.12.0 provider is not registered...while Platform cannot be changed from x64 to x86 - by der72

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 571116 Comments
Status Closed Workarounds
Type Bug Repros 8
Opened 6/26/2010 12:36:25 PM
Access Restriction Public


I have a Windows 7 64 Bit system with SQL Server 2008 R2 installed (which includes VS 2008) and I cannot establish a connection to a simple Access database sitting on the desktop of the very same computer. (Note that I use Office 2010 64 bit) No matter what I do I got the same error of "Microsoft.ACE.OLEDB.12.0 provider is not registered ..."
I downloaded from Microsoft the latest 64 bit AccessDatabaseEngine (Microsoft.ACE.OLEDB.14.0) because I cannot use the 32 bit version with Office 2010 64 bit.

I went through all the posting and could not find a solution.

So, let me summarize this simple scenario:

1) I have: a) Windows 7 64bit Pro, b) SQL Server 2008 R2 including VS 2008, c) Office 2010 64bit, and d) installed AccessDatabaseEngine_X64 (from MS).

2) I have an ACCESS database copied on the desktop of the very same computer

3) I would like to establish a database connection to the ACCESS database on the desktop with Analysis services to build some test cubes

4) Problem: I do not have the option of changing the Platform (under the Build > Configuration Manager > "Active Solution Platform" = "new"....). It does not have the drop down list and no matter what I set. See picture here:

5) Results: error message "Microsoft.ACE.OLEDB.12.0 provider is not registered..."

How should I do it? I cannot change the Platform as you can see from the picture. 

I am afraid that this blanked out Platform drop down list is another bug of the SQL Server 2008 R2. (Unofrtunately I already reported one earlier with the blank Function names under the Calculation tab. Maybe the two issues are related?)

OTOH, maybe I missed something and/or did not do it right so any help and instructions would be greatly appreciated. But I suggest to setup the same scenario and test it first.

Sign in to post a comment.
Posted by Microsoft on 7/12/2010 at 5:07 PM
The problem looks to be with missing or not clear instructions on installing ACE OLEDB provider and 32/64 bit OS applications.

Serveral facts.
BIDS is 32bit application.
Installed by default on 64 bit - Analysis Server is 64 bit application.

ACE OLEDB provider needs to be installed in ether 32 or 64bit mode. Single installation does not include both.

In your case you could use BIDS and install ACE OLEDB provider 32bit version on Dev machine.
Deply to another machine where you have Analysis Server and 64bit ACE OLEDB proivider installed.

Thanks reporting the problem.
Edward Melomed.

Posted by der72 on 6/29/2010 at 7:02 PM
Jamie, not the SQL Server 2008 R2 was the problem but the BIDS part. So when you opened a new project and if you had the AccessdatabaseEngine 2010 x64 you couldn't connect to an .accdb file because you did not ave the ace 12.0. Try it out, open an fresh analysis services project ....

Is your excel 64 bit? Try to load on the x64 accessdatabaseengine the 2007 version. This will solve your problem since mine was very similar except I had to deal with an access database.
Posted by Jamie Clayton on 6/29/2010 at 5:08 PM
If you open the Start -> SQL Server 2008 -> Import and Export Data x64 menu you get a Microsoft Office 12 Access option but no Excel 12 option. Apparently the Office 14 drivers use the same connection strings as Office 12 drivers. It doesn't resolve my issue of importing excel data SQL server via x64 drivers.
Posted by Jamie Clayton on 6/29/2010 at 4:31 PM
When you look at SSMS in Task manager of x64 machines it has SSMS.exe *32 (SQL 2008, not R2 edition) indicating to me the IDE is a x86 rather than x64 compiled application. My understanding is x64 apps can consume x32 applications but not the other way around. Looks like the VS 2010 IDE is also *32.
Surely Microsoft would have tested SQL Server and Office 2010 x64 data importing before releasing this software.
Posted by der72 on 6/29/2010 at 7:01 AM
>>>> Good News = The missing drop-down list (see link above to picture) is not a bug, it is designed in that way.

>>>> Bad News = Apparently we have the following options to connect to an Access (*accdb) database:

Option1: using Jet provider. Open Access database, and save database as Access 2002-2003 database (*.mdb) – click button ‘Save & Publish’. After that, use Microsoft.Jet.OLEDB.4.0 to connect to the file.

Option2: if there is new feature so you cannot convert it to Access 2002-2003 database, then you have to create this project in another 32bit office environment, after that, deploy the project to the server, and then process it in the server. Note: after deployment, you need to change the datasource in SSMS because of the file path.

This means that if you have the following:
1) Windows 7 64bit
2) SQL Server 2008 R2
3) Office 2010 64bit

4) an Access 2007 database (*.accdb) sitting on the same computer's desktop (or anywhere else)

Then your options are to go through one of these tricks:
a) Save it in an older format (*.mdb) if it is possible then use the Jet.OLEDB.4.0 driver to connect to it, or
b) Re-create the same Access project in another 32bit office environment, deploy the it to a server, and then process it in the server.

In my case, my problems are:

1) I have functions in the queries that do not work in the *.mdb format and the older (*.mdb) format has its limitations when it comes to the amount data it can store. I already surpassed that so this option is out.

2) I cannot re-create the project in another environment and post it on a server and process it there. The whole purpose of this is to quickly create sample testings while for example I travel and do not have access to any of our servers. So this option is out as well.

Now, is there any other way to make this SQL Server 2008 R2 with the BIDS to recognize 32bit and 64bit drivers somehow?