none
[ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed RRS feed

  • Question

  • Hello,

    I am trying to connect Excel with DB2 database trough ADODB (ODBC) and whilst the code works under Windows 7 x64 + Office 32-bit, it does not work under Windows 10 x64 + Office 32-bit.

    Dim dbConString As String, query As String
        Dim DBCON As Variant, dbRecSet As Variant
        
        
        'prod
        dbConString = "Driver={IBM DB2 ODBC DRIVER};Database=BMSFORMS;hostname=9.212.156.27;port=50000;protocol=TCPIP; uid=bmsforms; pwd=pwd123"
        
    
        query = "SELECT Req_Id, 'UPDWI', req_cty, company_cd, wi_id, '', lob, Rtrim(Ltrim(title)), start_date, end_date, closed_date, wi_status, upd_activities, add_activities, '', COMMENT FROM CSHQDM.CSHVC_BMS_CHANGEWI WHERE (SUBMISSION_TS >= current timestamp - 8 months AND Status = 'SW' AND Assign_Empno='" & serial & "' AND Assign_Cty = '" & cty & "' AND LTRIM(RTRIM(add_activities)) != '' AND Req_Id||'UPDWI' NOT IN (select req_id||req_type from CSHQDM.CSHVC_BMS_API)) ORDER BY Submission_TS "
        Set DBCON = CreateObject("ADODB.Connection")
        DBCON.ConnectionString = dbConString
        DBCON.Open
        
        Set dbRecSet = CreateObject("ADODB.Recordset")
        With dbRecSet
            .Source = query
        Set .ActiveConnection = DBCON
            .Open
            Sheets("Requests").Range("A7").CopyFromRecordset dbRecSet
            .Close
        End With

    The code works fine until DBCON.Open, then produces following error:

    [ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

    ODBC data source seems to be connected ok (connection can be established when testing). Can you please advise what to do?

    Thursday, November 29, 2018 1:58 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, November 30, 2018 2:10 AM
  • Hi Milan,

    Please follow the following steps:

    1. Right Click on My Computer and Click Properties.


    2. Navigate to Advanced Tab and click Environment variables.


    3. Under the System Variables List select the "Path" variable and click the Edit Button.

    On your system you can change the Path variable. Please refer to following example:

    --FROM

    E:\Oracle10OWB\bin;E:\Oracle10\bin;c:\Program Files\Oracle\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Intel\DMIX

    --TO

    E:\Oracle10\bin;E:\Oracle10OWB\bin;c:\Program Files\Oracle\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Intel\DMIX

    For more information, please see the following links:

    Driver's SQLAllocHandle on SQL_Handle_ENV Failed

    [Microsoft] [ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

    Driver's SQLAllocHandle on SQL_HANDLE_ENV Failed

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Friday, November 30, 2018 2:24 AM
    Friday, November 30, 2018 2:18 AM
  • HI Milan,

     

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, December 4, 2018 3:10 AM