ODBC DRIVER MANAGER: DATA SOURCENAME TOO LONG

Question

I am using an Access db in order to retrieve records from Oracle 12 db. We've recently upgraded from Office 2016 to Office 365. Since that day I can no longer establish a connection via the class module.

Private Sub Class_Initialize()
    Set mCnn = New ADODB.Connection
    
    With mCnn
        '.CursorLocation = adUseClient
        .CursorLocation = adUseServer
        .mode = adModeRead
        .IsolationLevel = adXactIsolated
        .CommandTimeout = 200
        
        If PROMPT Then
            .Properties("Prompt") = adPromptAlways ' if user must give password to connect
        Else
            .Properties("Prompt") = adPromptNever ' if there is no need of password
        End If
           
    End With
End Sub

Sub OpenConnection(s As String)
    Dim msg As String, _
        db As String, _
        app_instance As String, _
        connstring As String
    
    On Error GoTo errHandler:

    Dim usr As String
    
    usr = Environ("UserName")

    #If Win64 Then
      
        'O365 64 bit drivers/Providers        
        ' OracleConnection = "Provider=OraOLEDB.Oracle;Password=" & pw & ";User ID=" & uID & ";Data Source=" & HOST & ":" & portNo & "/" & sSID & ";Persist Security Info=True"

        connstring = "Provider=OraOLEDB.Oracle;Password=" & pw & ";User ID=" & uid & ";Data Source=" & HostIPSSID & ";"
            
        mCnn.ConnectionString = connstring
    #Else
        ' for other non 64bits Windows versions
        connstring = "Driver={Microsoft ODBC for Oracle};Server=" & server & ";UID=" & uid & ";PWD=" & pw
        mCnn.ConnectionString = connstring
        
    #End If

    mCnn.Open

    Exit Sub

end sub

.... however if I paste the code into a single sub it's not a problem - the connection string is 100% identical

Public Sub testttt()
    Dim strSQL
    Dim strUsername
    Dim strPassword
    Dim snpData
    Dim dbMyDBConnection As ADODB.Connection
    Dim connstring As String
    
    Set dbMyDBConnection = CreateObject("ADODB.Connection")
    strUsername = "dfagsdgasd"
    strPassword = "asdgasdgd"
    
    connstring = "Provider=OraOLEDB.Oracle;Password=" & strPassword & ";User ID=" & strUsername & ";Data Source=<SAME AS ABOVE>;Persist Security Info=True"

    dbMyDBConnection.ConnectionString = connstring
    dbMyDBConnection.Open
    
    dbMyDBConnection.Close
    
    Set snpData = Nothing
    Set dbMyDBConnection = Nothing

End Sub

Any idea?

HostIPSSID is a property (?) of the ADODB.connection and looks as follows: servername — Hochwald, Aug 31 '21 at 06:41
HostIPSSID is a property (?) of the ADODB.connection and looks as follows: :/ A, B, and C have been taken from tnsnames.ora = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ) ) ) — Hochwald, Aug 31 '21 at 06:48
I don't think the "easy connect" format `hostname:port/service` is supported in the ADODB. — Wernfried Domscheit, Aug 31 '21 at 07:54
well as I explained it works fine using the testttt() procedure. All my colleagues use the string. Do you any better way idea? — Hochwald, Aug 31 '21 at 12:41
I have tried shortened versions, i.e. referring simply to or a shorted version of the hostname i.e. CRD as opposed to CRD.EMEA..net. Nothing works. In both instances the driver returns the following error message: -2147467259 - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified — Hochwald, Aug 31 '21 at 12:59
I am a bit surprised, because you get an **ODBC** error while using `Provider=OraOLEDB.Oracle` - which is not ODBC! — Wernfried Domscheit, Aug 31 '21 at 14:14
surprising it may be but that's the error message, but I agree in excel I would choose Data / Get Data / From Other Sources / from OLE DB. Establishing a connection worked like a charm. I used the same connection string as in Access — Hochwald, Aug 31 '21 at 15:33

score 0 · Answer 1 · answered Aug 30 '21 at 17:46

Not a real answer to your question, but too long for a comment:

If you use the 32-bit or the 64-bit driver is not given by your Windows operating system, it depends on your OMS Office/ MS-Access

The OraOLEDB.Oracle provider is available as 32-bit version and as 64-bit version. The (deprecated) Microsoft ODBC for Oracle driver exists only for 32-bit. Maybe have a look at How to connect to Oracle 11 database from . net

Note, all the drivers/provides needs an Oracle client installed. In case you like to install both, 32-bit and 64-bit at the same time follow this instruction: BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Of course, a 32-bit Windows supports only the 32-bit driver - but we are in 2021 and 32-bit Windows are not that common anymore.

we have replaced the old 32-bit Oracle client with the respective 64 bit version. I can easily connect to oracle using sqlplus. What confuses me is that connection via Public Sub testttt() does work but the class module which in my opinion contains much the same code fails with error message mentioned in the title of the post. — Hochwald, Aug 31 '21 at 06:39

ODBC DRIVER MANAGER: DATA SOURCENAME TOO LONG

1 Answers1