none
Excel VBA Macro Connection to Teradata not working with Office 2016 upgrade RRS feed

  • Question

  • I have the following VBA macro which was effective in office 2010 in getting a connection to Teradata, but it's not working after upgrading to office 2016:

    Public Function Teradata_Connection()
    Dim constr
    pwd = frmTDconnection.txtPwd.Text
    uid = frmTDconnection.txtUsr.Text

    On Error GoTo Invalid
    constr = "Driver={Teradata};DBCNAME=oneview;UID=" & uid & ";PWD=" & pwd & ";"
    Set Cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    If Cn.State <> 1 Then
        Cn.ConnectionTimeout = 60
        Cn.CommandTimeout = 3600
        Cn.Open constr
    End If
    Teradata_Connection = True
    Exit Function
    Invalid:
    MsgBox ("There was error number " & Err.Number & ".  This is " & Err.Description & ".")
    Exit Function
    End Function

    Since my Office 2016 upgrade, it no longer works.  I get the error message:

    "There was error number -214217843.  This is [Teradata][ODBC Teradata Driver] Not enough information to log on."

    The project has Microsoft ActiveX Data Objects 2.8 Library and Microsoft ActiveX Data Objects Recordset 2.8 Library as available references.  These were required for it to work in Excel 2010, and it creates a different error when they are not enabled in Excel 2016 (e.g. the macro won't compile).

    Is there any way to find out how to make the ADODB connection work in Excel 2016?

    • Moved by Bill_Stewart Monday, March 7, 2016 9:42 PM Move to more appropriate forum
    Monday, March 7, 2016 9:41 PM

Answers

  • >>>I checked, and I'm running 32 bit Excel 2016.  The connection was working fine with Excel 2010--do you think it's possible that was 64 bit?  I have a good connection using Teradata SQL assistant.  Can you give a few more hints of things to check and try?  How would I verify the 32 bit version of ODBC is installed?

    According to your description, you could use the Windows ODBC Data Source Administrator to check the version of the installed ODBC drivers:

    1.On the Start menu, click Control Panel.

    2.In Control Panel, click Administrative Tools.

    3.In Administrative Tools, click Data Sources (ODBC).

    4.In the ODBC Data Source Administrator, click the Drivers tab.

    In addition this is the forum to discuss questions and feedback for Excel for Developers, so I suggest that you could post your question on Teradata forum.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Sunday, March 20, 2016 2:16 PM
    Tuesday, March 8, 2016 6:02 AM

All replies

  • Be sure 32/64 is correct.

    Post in Teradata forum for help installing and using Teradata ODBC driver.


    \_(ツ)_/

    Monday, March 7, 2016 10:03 PM
  • Strange thing is that it worked fine in Excel 2010, but "broke" in Excel 2016.  Same version of Teradata.  Therefore, I would guess it's an Excel thing rather than Teradata?

    I posted here:  http://forums.teradata.com/forum/connectivity/excel-2016-vba-connection

    but haven't gotten any feedback yet.  Is there a better place to post?

    Monday, March 7, 2016 10:10 PM
  • If you are running 32 bit version of Office you need to be sure 32 bit version of ODBC is in stalled.  Be sure to test the connection before you try to use it with Office.

    \_(ツ)_/

    Monday, March 7, 2016 10:18 PM
  • I checked, and I'm running 32 bit Excel 2016.  The connection was working fine with Excel 2010--do you think it's possible that was 64 bit?  I have a good connection using Teradata SQL assistant.  Can you give a few more hints of things to check and try?  How would I verify the 32 bit version of ODBC is installed?

    Tuesday, March 8, 2016 1:21 AM
  • >>>I checked, and I'm running 32 bit Excel 2016.  The connection was working fine with Excel 2010--do you think it's possible that was 64 bit?  I have a good connection using Teradata SQL assistant.  Can you give a few more hints of things to check and try?  How would I verify the 32 bit version of ODBC is installed?

    According to your description, you could use the Windows ODBC Data Source Administrator to check the version of the installed ODBC drivers:

    1.On the Start menu, click Control Panel.

    2.In Control Panel, click Administrative Tools.

    3.In Administrative Tools, click Data Sources (ODBC).

    4.In the ODBC Data Source Administrator, click the Drivers tab.

    In addition this is the forum to discuss questions and feedback for Excel for Developers, so I suggest that you could post your question on Teradata forum.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Sunday, March 20, 2016 2:16 PM
    Tuesday, March 8, 2016 6:02 AM
  • This is not a Teradata issue. Absolutely a MSFT issue. Can someone from MSFT please explain why the Excel VBA ODBC Macro issue was not present and functions in Excel 2010 but not Excel 2016?
    Tuesday, August 9, 2016 8:41 PM
  • Did  you install the correct drivers?  Windows 7 and later require special drivers to be installed.

    \_(ツ)_/

    Tuesday, August 9, 2016 8:51 PM
  • Special drivers? That's pretty vague...are you speaking of the ODBC driver or? Which drivers are you referring to?
    Tuesday, August 23, 2016 5:07 PM
  • Special drivers? That's pretty vague...are you speaking of the ODBC driver or? Which drivers are you referring to?

    You need the newer TeraData drivers.  Post in Teradata forum for assistance.  They will also show you how to test the drivers.


    \_(ツ)_/

    Tuesday, August 23, 2016 5:11 PM