Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Programmatically adding ODBC driver using VBA

979641
Member Posts: 2
Hi -
I have a VBA macro that connects to an Oracle db and pulls data into Excel using ADO. It uses the Provider 'OraOLEDB.Oracle.1'. The macro runs fine on my system. I am now giving this macro to others in my organization so they can also use it. However, the other machines do not have Oracle client loaded on their systems. The macro stops at the Connection.Open line because it does not recognize "OraOLEDB.Oracle.1"
So, here are my questions:
1. What drivers do I need to provide to the other users so that the macro can run?
2. Does the other system have to have Oracle client loaded or can I just load the driver software (OraOLEDB.Oracle.1)?
3. Can I programmatically check if the other system has the driver and if not, programmatically load the driver?
4. Is it possible to attach the OraOLEDB.Oracle.1 code as a file and then have the VBA code install it if needed?
My VBA code looks like this:
Set Connection = New ADODB.Connection
Connection.ConnectionTimeout = 20
Connection.CommandTimeout = 15
Cnct = "*Provider=OraOLEDB.Oracle.1*; Data Source=PDW; User Id=<user_id>; Password=<password>"
Connection.Open ConnectionString:=Cnct
Here is a list of the reference libraries used in this VBA project:
Reference Description
Visual Basic For Applications
Microsoft Excel 14.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office 14.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Script Control 1.0
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects Recordset 2.8 Library
Thanks for your guidance.
Gary
I have a VBA macro that connects to an Oracle db and pulls data into Excel using ADO. It uses the Provider 'OraOLEDB.Oracle.1'. The macro runs fine on my system. I am now giving this macro to others in my organization so they can also use it. However, the other machines do not have Oracle client loaded on their systems. The macro stops at the Connection.Open line because it does not recognize "OraOLEDB.Oracle.1"
So, here are my questions:
1. What drivers do I need to provide to the other users so that the macro can run?
2. Does the other system have to have Oracle client loaded or can I just load the driver software (OraOLEDB.Oracle.1)?
3. Can I programmatically check if the other system has the driver and if not, programmatically load the driver?
4. Is it possible to attach the OraOLEDB.Oracle.1 code as a file and then have the VBA code install it if needed?
My VBA code looks like this:
Set Connection = New ADODB.Connection
Connection.ConnectionTimeout = 20
Connection.CommandTimeout = 15
Cnct = "*Provider=OraOLEDB.Oracle.1*; Data Source=PDW; User Id=<user_id>; Password=<password>"
Connection.Open ConnectionString:=Cnct
Here is a list of the reference libraries used in this VBA project:
Reference Description
Visual Basic For Applications
Microsoft Excel 14.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office 14.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Script Control 1.0
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects Recordset 2.8 Library
Thanks for your guidance.
Gary
Answers
-
OraOLEDB is part of the Oracle client and relies on the client, so yes you'll need to have the Oracle client installed. I don't really know enough (aka: anything) about VBA to answer the other questions, sorry.
-
Tridus - thanks for your reply. I was concerned that Oracle Client would have to be downloaded to each machine that wants to run this macro.
What is OraOLEDB? Is it a single file? Is it a library of calls? What exactly is it? -
Why do you want to add "ODBC driver" when you are using OraOLEDB?
How do you plan to solve other requirements such as "Visual Basic For Applications"?
Perhaps you could ship Instant Client with your Excel application?
Then use Windows included interface libraries "for Oracle", or Instant Client ODBC, or include Xcopy deployment of data provider for OLE DB (OraOLEDB) from ODAC (builds on Instant Client).
See:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html (32-bit)
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html (64-bit) -
976638 wrote:OraOLEDB is Oracle's OLE DB database provider. It's kind of like the ODBC driver except for OLE DB, which is a different interface. I don't know the details of how many files it is, but it's included in the Oracle driver for Windows.
Tridus - thanks for your reply. I was concerned that Oracle Client would have to be downloaded to each machine that wants to run this macro.
What is OraOLEDB? Is it a single file? Is it a library of calls? What exactly is it? -
Hello
Would you be so kind to share your vba code to connect to Oracle Db thru odbc?
Thanks
This discussion has been closed.