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

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.ConnectionSet conn = New adodb.ConnectionconnStr = "Provider=OraOLEDB.Oracle;Data Source=DEMO;User Id=yourUserID;Password=yourPassword;"conn.Open connStrDim api As New adodb.RecordsetSet api = New adodb.RecordsetyourQueryString = "SELECT foo FROM bar"api.Open yourQueryString, conn, adOpenDynamic, adLockReadOnly 'adjust above setting as neededwhile not api.EOF  'do interesting stuff herewend'clean up resourcesapi.CloseSet api = Nothingconn.CloseSet 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:

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.

' Create a connection object.'Dim cn As ADODB.ConnectionSet cn = New ADODB.Connection' Create a recordset object.'Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordset' Provide the connection string.'Dim strConn As StringDim 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 strConnWith rs    ' Assign the Connection object.'    ActiveConnection = cn    ' Extract the required records.'    .Open "SELECT ", cnEnd With