Unable to access different DB's

[JohnK123]

I am trying to access 2 different server DB's but consistently get error messages trying to access the second DB.
One DB is ORACLE the other ACCESS.
After accessing ORACLE I can no longer access ACCESS and if I retrieve from ACCESS first, I cannot retrieve from ORACLE.
Stepping through each option works fine provide I close all open workbooks prior to attempting access to the other.
Individually the connections and reading of fields within the tables is working as expected.
When trying to retrieve from ACCESS after ORACLE I get a '1004 error'. Trying ORACLE after Access I get a 'Hang & Restart Excel' message or Error 'The object Invoked Has Been Disconnected.....' which results in a hang!
I have tried calls from another workbook, connection strings, reducing the DB calls to a minimum, accessing ACCESS before ORACLE, Visa Versa, all fail.
I need some real help.

Thanks to this who can help.


Connection String to ORACLE

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _        "ODBC;DSN=ORACLE_SVR;UID=******;PWD=******;DBQ=A11;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSucce" _
        ), Array( _
        "ssful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;")), _
        Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _

Connection String to ACCESS

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _        "ODBC;DSN=ProntoDSN;UID=****;PWD=****;", Destination:=Range("$A$3")). _
        QueryTable
        .CommandText = Array( _
        "SELECT fms_con_note_header.cn_avail_date, fms_manifest.manif_nbr, fms_con_note_header.

 

[mole999]

maybe this can help > https://msdn.microsoft.com/en-us/li...aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

In excel I use

   With Sheets("CLOSED")
        .Select
        .Range("A9") = "Case"
        .Range("B9") = "Status"
        .Range("C9") = "Date Closed"
    End With

    Set rnStart = Sheets("CLOSED").Range("A10")

    EMPLOYEEID = Cells(7, 1).Value
    getuser = EMPLOYEEID

    sConn = "Provider=sqloledb;Password=sqlserv;Persist Security Info=True;User ID=t***n;Data Source=h***s"

    Var1 = ""
    Var1 = Var1 & "Select accinv_rec.las_case_no " & vbCrLf
    Var1 = Var1 & "       , accinv_rec.status_2 " & vbCrLf
    Var1 = Var1 & "       , accinv_rec.date_file_closed " & vbCrLf
    Var1 = Var1 & "From   accinv_rec " & vbCrLf
    Var1 = Var1 & "Order  By accinv_rec.las_case_no Desc "

    sSql = Var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    adoConn.CommandTimeout = 60
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, ActiveConnection:=adoConn
    On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF
            rnStart.CopyFromRecordset adoRs
            adoRs.MoveNext
        Loop
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "NO MATCHED  DATA"
        Sheets("CLOSED").Range("A10") = sOutput
    End If
    adoRs.Close
    adoConn.Close
    Set adoRs = Nothing
    Set adoConn = Nothing
    Sheets("CLOSED").Columns("A:C").EntireColumn.AutoFit
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Range("A9:C9").Select
    Selection.AutoFilter
    On Error Resume Next
    ActiveWindow.FreezePanes = False
    Range("B10").Select
    ActiveWindow.FreezePanes = True

a little untidy, but easy to maintain as long as you are not trying to do a union between databases