Oracle VBA connection string
How does one properly establish a connection string for integrating a macro-enabled PowerPoint to be able to query an Oracle database? I have seen many old youtube videos and online tutorials regarding this (mostly in excel), but the connection string appears differently in each one. I am using a 64 bit Windows machine and an Oracle database that I usually access through SQL Developer.
A reference link that appears to be in the right direction:
http://learnexcelmacro.com/wp/2011/11/oracle-connection-string-in-vba/
I am trying to use a connection with the service, so it should look something like this:
The only changes that I have personally made to the connection string are: HOST is Hostname founder in SQL Developer instance properties Port is Port in SQL Developer instance properties Uid is username in SQL Developer instance properties Pwd is password in SQL developer instance properties
I have left all other fields unchanged. It should be noted that I do in fact have Tools>References>Microsoft ActiveX Data Objects 2.8 Library
I understand that inside of SQL Developer you can view the properties for a database instance connection. Is all the information there to construct my connection string? I cannot get the connection string to work and I am constantly met with errors.
Thanks!
There are several drivers available to connect to an Oracle database from VBA.
Common are ODBC Driver, either from Microsoft or from Oracle or the OLE DB provider. OLE DB provider are also either from Microsoft or from Oracle. There are even a few more from 3rd party (see How to connect to Oracle 11 database from . net), but lets keep it simple.
Depending on which driver you choose, the connection string is different:
- ODBC Driver from Microsoft:
Driver={Microsoft ODBC for Oracle};... - ODBC Drvier from Oracle:
Driver={Oracle in OraClient11g_home1};...(or similar) - OLE DB Provider from Microsoft:
Provider=MSDAORA;... - OLE DB Provider from Oracle:
Provider=OraOLEDB.Oracle;...
For details have a look at Oracle Connection Strings or Connection String Home Page
All these drivers/providers require at least an Oracle Instant client installed on your machine. When your PowerPoint is 32-bit then also the Oracle client and as well the driver/provider must be 32-bit. When your PowerPoint is 64-bit then all other components must be also 64-bit - you cannot mix 32-bit and 64-bit.
The Oracle drivers provided by Microsoft are deprecated for ages, they are not available for 64-bit! So, when you have a 64-bit Office then {Microsoft ODBC for Oracle} and MSDAORA are out of the game.