EXCEL/VBA/TERADATA - Pull a scalar value from Teradata into an Excel cell

Option Explicit

Private con As ADODB.Connection

Private cst As String

Private rst As ADODB.Recordset

Private DBFunctionsMenuSetupFrequencyCount As Integer

Private DBConnectionFrequencyCount As Integer

' 1. Connection

    ' 1.1. Start

    Public Sub setDBConnection()

        ' Exit if the connection is already present

        If Not IsEmpty(DBConnectionFrequencyCount) _

            And DBConnectionFrequencyCount > 0 _

        Then

            'Exit Sub

        End If

       

        ' Open the connection

        Set con = New ADODB.Connection

        con.ConnectionTimeout = 3 'Seconds

        con.CursorLocation = adUseClient

        con.IsolationLevel = adXactReadUncommitted

        cst = _

            "Data Source=DB_ODBC_NAME; " & _

            "Database=DATABASE_NAME; " & _

            "Session Mode=TERA;" 'or ANSI (Teradata)

        con.Open cst

       

        ' Generic Recordset Declaration

        Set rst = New ADODB.Recordset

        rst.CursorLocation = adUseClient

        rst.CursorType = adOpenForwardOnly

        rst.LockType = adLockReadOnly

        rst.MaxRecords = gloMaxRecords

       

        ' Count the number of times a connection has been made

        DBConnectionFrequencyCount = DBConnectionFrequencyCount + 1

    End Sub

   

   

    ' 1.2. Terminate

    Public Sub getDBConnectionTerminated()

        Set con = Nothing

        DBConnectionFrequencyCount = 0

    End Sub

' 2.1. getCustomerName

Public Function getCustomerName( customerId As String) As String

   

    ' SQL

    Dim sql As String

    sql = _

        "SELECT max( clientName ) as Result " & _

        "FROM " & _

            "CLIENTS " & _

        "WHERE " & _

            "clientId='" & customerId & "' " & _

        ";"

       

    ' Open connection and recordset

    Call setDBConnection

    rst.Open sql, con

    'Debug.Print rst.ActiveConnection.ConnectionString   

    ' Use the recordset

    Do While Not rst.EOF

        getCustomerName = rst.Fields("Result")

        rst.MoveNext

    Loop

    ' Exit, cleanup

    rst.Close

    'getDBConnectionTerminated

    con.Close

    Exit Function

  

End Function