PDA

View Full Version : Using Excel to Connect to Oracle



cboshdave
12-13-2013, 07:02 AM
I have read countless postings on connecting to Oracle and I am still stumped. One problem may be that I am running a 64 bit machine with 32 bit Oracle installed trying to make everything work.

My Connection String looks like this:



Set oCon = New ADODB.Connection
sPW = InputBox("Please enter the password.", "Password Req'd")
Set Connection = CreateObject("ADODB.Connection")
With oCon
.Provider = "OraOLEDB.Oracle"
.Properties("Data Source") = "SchemaName"
.Properties("User ID") = "username"
.Properties("Password") = sPW '"password"
.Open
End With

My References window looks like this:

10960
Well, that's not gonna work. The checked items are:
VB for Apps
Microsoft Excel 14.0 Object Lib
OLE Automation
MS Office 14.0 Object Lib
MS ActiveX Data objects 2.8 Lib
MS ADO Ext 2.8 for DDL and Security
MS Data Access Components Installed Version
MS OLE DB Service Component 1.0 Type Lib
MS OLE DB Simple Provider 1.5 Lib

and the error I get is still:
Runtime Error 3706. Provider cannot be found, when it hits .Properties("Data Source") = "SchemaName".

Any suggestions?? This same code works on other machines, but I don't know why.

Kenneth Hobs
12-13-2013, 08:13 AM
Did you try http://www.connectionstrings.com/oracle/
or
http://www.vbaexpress.com/forum/showthread.php?40986-Solved-Connect-to-Oracle-Database-using-Excel-VBA

Bob Phillips
12-13-2013, 08:29 AM
Are you sure that you have the Oracle OLEDB driver installed on your machine?

Why are you connecting twice?

cboshdave
12-13-2013, 11:22 AM
Are you sure that you have the Oracle OLEDB driver installed on your machine?

Why are you connecting twice?

Thanks xld.

You ask why I am connecting twice? Not sure why that is?
I also verified that I have OraOleDB11*.dll exist in my BIN folder. A whole bunch of files seem to be in there. How do I verify if the .dll was actually registered on my machine? Also, everyone seems to refer to OraOLEDB11.dll. I don't have that actual file. I have OraOLEDB11us.dll.

Bob Phillips
12-13-2013, 04:17 PM
I would guess that OraOLEDB11us.dll is just a US specific version, with the software exporting restriction you have there might be something Oracle deem not appropriate to export (just summising).

Rather than bother checking if it is registered, just re-register it, but I would bet it is registered if you have a full BIN of files.

Where have you specified where the database server is and what it is called?

cboshdave
12-13-2013, 04:22 PM
The database is specified in the TNSNames.ora file.