crmulzer
asked on
Excel VBA ADODB connecting to Oracle Instant Client
I'm writing a little Excel VBA application on Windows XP that connects to a remote Oracle database. I have it working using the standard Oracle client using this connection string:
oc.Open "Provider=msdaora;" & _
"Data Source=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
But I run into a problem when I try to use Oracle Instant Client. The software is here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
I installed the Instant Client software and the additional ODBC files in the directory instantclient_11_2. I then added the instantclient_11_2 directory to the Path variable. I then copied the tnsnames.ora and sqlnet.ora files into another directory and set the variable TNS_ADMIN to point to this directory. These were the instructions given in the Readme file that came with the ODBC files downloaded from the above site.
I then tried this connection string which I assume is the problem.
oc.Open "Driver={Oracle in instantclient_11_2};" & _
"Data Source=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
Can someone please let me know where I went wrong? The error I get indicates that the Data Source name isn't found and no default driver specified so basically nothing is working with my setup.
Thanks.
oc.Open "Provider=msdaora;" & _
"Data Source=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
But I run into a problem when I try to use Oracle Instant Client. The software is here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
I installed the Instant Client software and the additional ODBC files in the directory instantclient_11_2. I then added the instantclient_11_2 directory to the Path variable. I then copied the tnsnames.ora and sqlnet.ora files into another directory and set the variable TNS_ADMIN to point to this directory. These were the instructions given in the Readme file that came with the ODBC files downloaded from the above site.
I then tried this connection string which I assume is the problem.
oc.Open "Driver={Oracle in instantclient_11_2};" & _
"Data Source=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
Can someone please let me know where I went wrong? The error I get indicates that the Data Source name isn't found and no default driver specified so basically nothing is working with my setup.
Thanks.
ASKER
Yes, I did run odbc_install.exe and I see the driver listed when I look at the ODBC Drivers. It's listed under the name "Oracle in instantclient_11_2".
In the connection string for standard Oracle above (which works for me) I have the Provider set to "msdaora". Where does that name come from? I'd like to find out what the Provider should be set to for Oracle Instantclient.
Thanks.
In the connection string for standard Oracle above (which works for me) I have the Provider set to "msdaora". Where does that name come from? I'd like to find out what the Provider should be set to for Oracle Instantclient.
Thanks.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I finally, through trial and error, stumbled upon a connection string that works. I hadn't realized that one driver might use "Data Source" to refer to the database in the tnsnames file while another driver might use "Dbq". Anyway, here is a connection string that worked for me with the Oracle Instant Client driver:
oc.Open "Driver={Oracle in instantclient_11_2};" & _
"Dbq=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
oc.Open "Driver={Oracle in instantclient_11_2};" & _
"Dbq=MyLittleDB;" & _
"User Id=myuid;" & _
"Password=mypass"
Glad you got it working. Don't forget to close this out.
Did you 'install' the ODBC drivers? If memoery serves there is a BAT install for the ODBC add-on to ths instant client.