Example of using Teradata Database System as a data source in Visual Basic 6.0

The following example uses Visual Basic 6.0 and ActiveX Database Objects (ADO) 2.5  to access a Teradata Database System using the Teradata ODBC driver.

To use the following code from a Windows platform, the user must first establish an ODBC system data source.  This data source stores information about how to connect to the Teradata Database System.   The user must also have the appropriate Teradata ODBC driver installed on the client computer being used to access the Teradata Database.

Instructions to install and configure the Teradata Database System Windows Client and the ODBC driver can be found here.

For the below example, it is assumed that a system data source has been setup and it is named "TeradataDSN"

Static Embedded SQL Example

'Variables...
Private objConn As New ADODB.Connection    'Connection...
Private objRS As New ADODB.Recordset    'Recordset...
Private objErr As ADODB.Error    'Errors...

'Connect to database using DSL set in the ODBC setup...
objConn.Open "Driver=TeradataDSN; Server=Teradata Server Name; Database=Database Name; UID=User ID; PWD=Password"

'Check for errors...
If objConn.State = adStateOpen Then    'If connection succeeded, continue...

    objRS.Open "SELECT * FROM customer_service.contact;", objConn    'Issue SQL statement

    If Not objRS.EOF And Not objRS.BOF Then    'Check for End or Beginning of File
        Debug.Print objRS!State_Name    'Print the contents of the field...
    End If

    objRS.Close    'Close the recordset...

Else

    'Display errors..
    For Each objErr In objConn.Errors
        Debug.Print objErr.Description
    Next

End If

'Release connection to database...
Set objRS = Nothing
Set objConn = Nothing

Dynamic Embedded SQL Example

-  Assume that we are doing a query for multiple job_codes from the customer_service database.  The job_codes that we want to get information for are stored in an integer array called intaJobCodesRequested().

'Variables...
Private objConn As New ADODB.Connection    'Connection...
Private objRS As New ADODB.Recordset    'Recordset...
Private objErr As ADODB.Error    'Errors...

Dim strSQL as string

'Connect to database using DSL set in the ODBC setup...
objConn.Open "Driver=TeradataDSN; Server=Teradata Server Name; Database=Database Name; UID=User ID; PWD=Password"

'Check for errors...
If objConn.State = adStateOpen Then    'If connection succeeded, continue...

        'Dynamically build SQL statement
    strSQL = "SELECT * FROM customer_service.job WHERE"

    For x = 0 to Ubound(intaJobCodesRequested)   
        strSQL = strSQL & " job_code = " & intaJobCodesRequested(x) & " AND"
    Next x

    'Remove last AND statement and add a ;
    strSQL = Left(strSQL, Len(strSQL) - 4): strSQL = strSQL & ";"

    objRS.Open strSQL, objConn    'Issue SQL statement

    If Not objRS.EOF And Not objRS.BOF Then    'Check for End or Beginning of File
        Debug.Print objRS!State_Name    'Print the contents of the field...
    End If

    objRS.Close    'Close the recordset...

Else

    'Display errors..
    For Each objErr In objConn.Errors
        Debug.Print objErr.Description
    Next

End If

'Release connection to database...
Set objRS = Nothing
Set objConn = Nothing

User defined Dynamic Embedded SQL Example

-  Assume that we are doing a query for a user specified job code from the job_codes table in the customer_service database.  The job_codes that we want to get information for are stored in an integer alled inaJobCodesRequested.

'Variables...
Private objConn As New ADODB.Connection    'Connection...
Private objRS As New ADODB.Recordset    'Recordset...
Private objErr As ADODB.Error    'Errors...

Dim strSQL as String, intJobCodeRequested as Integer

'Prompt User for job code...
intJobCodeRequested = InputBox("Please enter the job code desired:  ", "Teradata Program", " ")

'Connect to database using DSL set in the ODBC setup...
objConn.Open "Driver=TeradataDSN; Server=Teradata Server Name; Database=Database Name; UID=User ID; PWD=Password"

'Check for errors...
If objConn.State = adStateOpen Then    'If connection succeeded, continue...

        'Dynamically build SQL statement
    strSQL = "SELECT * FROM customer_service.job WHERE job_code = " & intJobCodeRequested

    'Remove last AND statement and add a ;
    strSQL = Left(strSQL, Len(strSQL) - 4): strSQL = strSQL & ";"

    objRS.Open strSQL, objConn    'Issue SQL statement

    If Not objRS.EOF And Not objRS.BOF Then    'Check for End or Beginning of File
        Debug.Print objRS!State_Name    'Print the contents of the field...
    End If

    objRS.Close    'Close the recordset...

Else

    'Display errors..
    For Each objErr In objConn.Errors
        Debug.Print objErr.Description
    Next

End If

'Release connection to database...
Set objRS = Nothing
Set objConn = Nothing