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