Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


11
0
Sign in
to vote
Type: Bug
ID: 571116
Opened: 6/26/2010 12:36:25 PM
Access Restriction: Public
3
Workaround(s)
view
8
User(s) can reproduce this bug

Description

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: http://img821.imageshack.us/f/platform1.jpg/

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.

Thanks.
Details
Sign in to post a comment.
Posted by Microsoft on 7/12/2010 at 5:07 PM
der72.
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?
Sign in to post a workaround.
Posted by TomIncept on 8/11/2013 at 1:52 PM
I have been struggling with a 32 it application developed in Visual Studio 2005 useing the ACE.OleDB.12 engine.
I could not get it to run on a windows 8 Microsoft Surface as it kept saying the provider is not registered.

I could not install the 32 bit Access Database engine as the computer already had the 64 bit version installed, and it said that the 32bit could not be installed as currently the 64-bit office had been installed.

By creating a shortcut to the 32bit version of the AccessDatabaseEngine installation and including the "/passive" flag it installed simply and solved the problem.
Posted by AgaNemnon Max on 5/25/2012 at 12:16 AM
Hello, All!

This steps helps me install AccessDatabaseEngine_2010_x86 on Win7_64x with Office 2010_x64:
1. read an article
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
2. Setup AccessDatabaseEngine_2010_x86 from CMD with key "/passive"

Result: I have ability to create SSIS Packages On Win7_x64 with connect to Access files.
Posted by der72 on 6/29/2010 at 1:46 PM
Ok it seems to me that I cracked it. Here is how it can be done to have 32bit and 64bit ACE 12.0 and 14.0 drivers on the same computer.

Step1: Install AccessDatabaseEngine_2010_x64
Step2: Install Office 2010 x64
Step3: Open VisualStudio and leave open one analysis services project
Step4: Install AccessDatabaseEngine_2007_x86
Step5: Close everything, reboot, and now you can see in Visual Studio the ACE 12.0 driver

So far it is working for me but it would be great if others can test it and give feedback.