Alex Rivera | Logout

Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

Asked 2010-08-03T15:50:35.393
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...

excel vba oracle-database ms-access ado
Edit
Report

1 Answer

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
answered 2012-04-27T10:28:14.360

Your Answer