Currently, I have an excel 2010 application that users on Windows XP 32 bit are using to connect to an Oracle 32-bit database and extract data back into Excel. Each time they open the Excel file, the VBA code automatically creates a new connection using Provider =ORA.OLEDB.Oracle Provider. All users using the Excel file have installed Oracle 9.2.1 run-time client on their machine. All of that works well currently for over 100+ users on Windows XP 32-bit
Starting very shortly our company is moving to Windows 7 64-bit. We acquired a test PC with Windows 7 64-bit and Excel 2010 32-bit. We tried to install Oracle 9.0.2 run-time client but we couldn't connect to the database ( even with Sql Plus). We therefore downloaded Oracle 10g 10.2.04 Run-time client. With this installed, we were able to connect to via SQL Plus to the database. However, using the same Excel file and the following same VBA code.
Set cn = New ADODB.Connection
Public Function getDBCon()
getDBCon = "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=" & getDBUser() & ";Password=" & getDBPss()
I get a "Provider Not Found error". The VBA code and file work perfect in XP 32-bit. It seems going over to Windows 64-bit has caused issues with the file's macro. I am looking for help assessing if I have more of an Oracle Client issue or a Microsoft ADO issue. Any input would be greatly appreciated. Thank You.
The last time Oracle 9.anything was fully supported dinosaurs still roamed the earth.
Oracle 10.anything was put into extended support mode many months ago.
If you are going to do anything use currently supported versions ... that means 11.0.7 or 22.214.171.124.
That said I am still struggling to understand how, in 2012, anyone can justify connecting an Excel spreadsheet to an Oracle database. Consider using the right tool for the job ... and no matter what the job is ... Excel isn't it.
Yea agreed with Excel to Oracle comment. This is due to issues with global implementation of an ERP system and the disconnets between IT and Business. Therefore the business intelligence tables in oracle are most easily pulled and acted on by novice PC users via Excel. In the business, Excel is the standard tool novice non-IT people feel comfortable in to share and act on data. Most of these users have no IT knowledge whatsoever, so we create buttons and filters for them to query and then massage in Excel as they see fit.
Having said that, still wondering if it's a ADO or Oracle Client issue. This is something beyond my knowledge. I wouldn't imagine it's the Visual Basic issue since this same file works in Windows XP. I am not in the position to just scrap Excel as the business relies on this in production and I am the only support. What could 64 bit vs 32 bit differences be having here? Thank You.
If the data is from an ERP system and you are in either North America, Europe, or Japan (probably other countries too) you can not legally make decisions based on data where the providence is in question.
Oracle data is audited and secured. Once numbers are in an Excel spreadsheet there is no way to verify their accuracy.
Ok. I guess I am asking the wrong question perhaps. My question is not about legality of data or security. That would be a separate topic here. My concern is not about people altering data in Excel. This was a solution developed by a Gold Certified Oracle professional as a quick solution to get data to the business( fully aware there's better ways to do it).
My concern is why isn't the ORAOLEDB.Oracle provider working in a 64 bit environment based on the error the vba code is generating. I have installed the Oracle Client. The only difference is the 64-bit OS. Has anyone had similair issues? Thank You again. I am not an IT professional, but responsible for this, so any help fixing my particular problem would be much appreciated. Thank You.
I am having the same problem with a macro I am supporting, but my case is slightly different. The same macro works fine both in XP and Win7 (new machine). However, for those who just upgraded to Win7 with Oracle client already installed, the macro simply can't find the OraOLEDB provider. Were you able to find a solution to your problem back in March if don't mind sharing it? For now, I asked the user to get HelpDesk to reinstall Oracle 11g Client.