When trying to access an Oracle 12c Database through Excel and Power Query, I received an error stating
The recommended provider("Oracle.DataAccess.Client") is not installed. You
can continue with your current provider, however it has been deprecated and
may not work correctly.
I went through Microsoft's own guide (https://support.office.com/en-us/article/connect-to-an-oracle-database-power-query-d7fbd231-a705-4eb7-83b3-a66bfb678395?ui=en-US&rs=en-US&ad=US) to install the ODAC. My specs are:
Windows 10 64-Bit
Excel for Office 365 MSO (16.0.11425.20200) 64-bit
Oracle 12c
So, following the microsoft guide, I installed
64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64
(i've also tried releases 1 & 2 and the non-Xcopy versions of each, to no avail). The ODAC 12c Release 4 includes the following:
64-bit Oracle Data Provider for .NET 4 12.1.0.2.0
64-bit Oracle Data Provider for .NET 2.0 12.1.0.2.0
64-bit Oracle Providers for ASP.NET 4 12.1.0.2.0
64-bit Oracle Providers for ASP.NET 2.0 12.1.0.2.0
64-bit Oracle Provider for OLE DB 12.1.0.2.0
64-bit Oracle Services for Microsoft Transaction Server 12.1.0.2.0
64-bit Oracle Instant Client 12.1.0.2.0
When trying to connect to an Oracle database within Excel I get multiple errors:
First, I Click on
Data > Get Data > From Database > From Oracle Database
A messagebox/error appears stating
The recommended provider("Oracle.DataAccess.Client") is not installed. You
can continue with your current provider, however it has been deprecated and
may not work correctly.
I'm not sure why this message would appear, when I installed the Microsoft recommended ODAC. Nevertheless, I click
'OK'
An inputbox appears with title:
Oracle database
In the Server field I type:
%HOSTNAME%:%PORT%\%SERVICE_NAME%
A new error message appears:
"Unable to Connect"
"We encountered an error while tyring to connect."
Details: "Failed to find or load the registered .Net Framework Data
Provider.. This is a configuration issue with the file
'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config'."
At this point I am unable to continue and cannot connect to the Oracle database. Any and all insight would be greatly appreciated.