10

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include:

  1. Set up an Oracle Instant Client
  2. Install the accompanying ODBC driver
  3. (Test the connection using EZCONNECT via SQL Plus)
  4. (Test the connection by creating a Windows DSN)

Everything up to here works fine. Problem is, I cannot figure out the syntax to tell ADO to use the instant client ODBC driver, which appears in my ODBC driver list as "Oracle in MyTest" (no quotes). Using the MSFT ODBC driver with EZConnect as this post suggests does not work any better than it did prior to setting up the instant client (which is to say, not at all). But this post seems to suggest it's possible, without stating exactly how, and connectionstrings.com only tells you what the Data Source portion of the string looks like, i.e. SomeUser/SomePassword@AServer:PortNumber/InstanceName

Short version: What is the exact syntax of a classic ADO connection string referencing an instant client ODBC driver?

Thanks in advance for your help. Took me a stupid long time to get going with SO...

Erik A
  • 31,639
  • 12
  • 42
  • 67
downwitch
  • 1,362
  • 3
  • 19
  • 40
  • This post http://forums.oracle.com/forums/thread.jspa?threadID=621679&tstart=90 seems to explain why MSFT's ODBC driver doesn't like the ORCL instant client--it delivers dlls it doesn't support for ODBC without including their dependencies. Unfortunately nothing I could do would get MSFT's driver to see those dependencies even once I copied them in to the IC folder. – downwitch Aug 03 '10 at 20:31
  • At work (I am at home now, my timezone is CET) we do this all the time. There is (an existing) Access DB and sometimes when a new Oracle DB gets added I basically add in control center an ODBC connection (somwhat like described here http://support.microsoft.com/kb/303968) next I go and then go copy the passthru object in the Access DB and change the DB name in this endlessly long string. I guess you are looking for this long string. Well if your question is still unanswered tomorrow I might need to paste that string and it might help. – hol Aug 03 '10 at 20:38
  • Thanks for your help, but I'm actually looking to connect directly to the Oracle server in its "most native" syntax, which is either EZ Connect or a kind of connection-string embedded TNS like this article describes using http://www.codeproject.com/KB/vbscript/connection_string.aspx but which fails as I describe in my first comment. – downwitch Aug 03 '10 at 21:19
  • The idea is to have as small an Oracle and config footprint for an application as possible. The application files have to be installed, of course, but the client should be light (no mean feat in Oracleland, I am learning--even the "instant" client + the "instant" ODBC driver hits almost 100MB) and all pointers to servers, instances, and logins must live inside the app files. No TNS, no DSN (say that 10x fast!), etc. If possible. – downwitch Aug 03 '10 at 21:19
  • you try http://connectionstrings.com/oracle ? – Bruno Leite Sep 16 '11 at 23:09
  • Did you do this part : "After the installation the environment needs to be configured in the following way. Add the directory to the PATH system environment variable. Otherwise ODBC manager will be not able to load/find the driver." of the install instructions for the ODBC Instant Client? http://www.oracle.com/technetwork/database/features/oci/odbc-ic-releasenotes-094306.html – Brian Oct 24 '11 at 03:37
  • For future visitors... this old question still gets some activity, but I no longer have access to the environment I wrote about (and haven't for years), so I can't confirm any of these solutions. Until I land in another environment like it... – downwitch Jan 22 '16 at 14:37

3 Answers3

4

Similar to 'user1206604's answer - I set up an ODBC connection using ODBC Data Source Administrator (for example's sake we'll name it 'DEMO') and connect like this:

Dim conn As New adodb.Connection
Set conn = New adodb.Connection

connStr = "Provider=OraOLEDB.Oracle;Data Source=DEMO;User Id=yourUserID;Password=yourPassword;"
conn.Open connStr

Dim api As New adodb.Recordset
Set api = New adodb.Recordset

yourQueryString = "SELECT foo FROM bar"
api.Open yourQueryString, conn, adOpenDynamic, adLockReadOnly 
'adjust above setting as needed

while not api.EOF
  'do interesting stuff here
wend

'clean up resources
api.Close
Set api = Nothing

conn.Close
Set conn = Nothing

The ODBC data source administrator is found (on my machine) in start menu > Programs > Oracle - oraClient10g > Configuration and Migration Tools > Microsoft ODBC Administrator and looks like this:

ODBC Data Source Administrator

user8472
  • 3,268
  • 3
  • 35
  • 62
Richard
  • 51
  • 2
  • This saved my life. Thank you! Just had to declare "Dim connStr, yourQueryString As String" and add "api.MoveNext" to the loop. – SeaBass Sep 23 '15 at 22:40
3

Try this and replace the values as appropriate:

Set Connection = CreateObject("ADODB.Connection")

blnTest = Connection.Open("Driver={Oracle in instantclient};Dbq=127.0.0.1:1521/SERVICENAMEHERE", "USERNAME", "PASSWORD")

If Oracle in instantclient doesn't work check the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers registry key to see what the value is for the Oracle Instant Client (there may be a version number appended).

If this still doesn't work for you. Leave a comment with the details of what happened and I'll try to adjust the answer for you.

JamieSee
  • 12,696
  • 2
  • 31
  • 47
0
' Create a connection object.'
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

' Create a recordset object.'
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

' Provide the connection string.'
Dim strConn As String
Dim str As String

'Use the SQL Server OLE DB Provider.'
strConn = "Driver=(Oracle in OraHome92);" & "Data Source=;Uid=;Pwd=;"

'Now open the connection.'
cn.Open strConn
With rs

    ' Assign the Connection object.'
    ActiveConnection = cn

    ' Extract the required records.'
    .Open "SELECT ", cn


End With
iDevlop
  • 24,841
  • 11
  • 90
  • 149