I am also trying to connect MS Access to NCR Teradata. I have not been able to connect through ADODB connection - when I try to open the connection I get this error: "Can't find host [MyServer]Cop1

I am trying to connect MS Access to NCR Teradata.  Ultimately I want to use Access to send pass through queries to teradata and receive the results.  I have not been able to connect through ADODB connection.

Error1:  "Can't find host [MyServer]Cop1"  
Error2:  "10065 WSA E HostUnreach: The teradata server can't currently be reached over this network"

I'm trying to use a standard ADODB connection but it doesn't seem to be working...
Option Compare Database
Option Explicit
 
Public Sub RunPassThroughQuery() '(SQLString As String)
    Dim oConn As ADODB.Connection
    Dim oRec As ADODB.Recordset
    Dim conStr As String   'connection string
    Dim rConn As ADODB.Connection
    Dim strDir As String
    
    On Error GoTo ErrorHandle
    
    Set rConn = CurrentProject.Connection
    Set oConn = New ADODB.Connection
    
    Set oRec = New ADODB.Recordset
    
    With oConn
        .CursorLocation = adUseClient
        .CommandTimeout = 3600
        .Open "Driver={Teradata};DBCName=[P5450];Database=[IW_Deflt_ProdDB_V];Connect Timeout=3600;Uid= abc;Pwd= 123"
    End With
    
    'Run the SQL command on the Teradata warehouse
    'oRec.Open SQLString, oConn 'set other attributes if necessary
    
    If oRec.RecordCount < 1 Then
        MsgBox "There were no records to import.  This is probably a bug, please contact development."
        Exit Sub
    End If
    
    oRec.Close
    oConn.Close
    rConn.Close
    
    Exit Sub
    
ErrorHandle:
 
MsgBox Error()
Exit Sub
 
End Sub

Open in new window

BryceMTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BryceMTAuthor Commented:
I read through that post already -

I need help correcting the connection string or finding some way to establish a connection to Teradata through MS Access.  So far, I have successfully execute SQL pass through queries in my Access DB, but I have not been able to open up a connection in VBA with ADODB connection.
0
Rey Obrero (Capricorn1)Commented:
try this format from
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForTeradata

oConn.Open "Provider=Teradata;" & _
           "DBCName=MyDbcName;" & _
           "Database=MyDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

 or

http://www.connectionstrings.com/teradata#78

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BryceMTAuthor Commented:
After I tried this format:

oConn.Open "Provider=Teradata;" & _
           "DBCName=MyDbcName;" & _
           "Database=MyDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

I'm getting this error:
"Provider cannot be found - it may not be properly installed."
0
BryceMTAuthor Commented:
I might need to install a Teradata driver...  Has anybody worked with installing teradata drivers in ms access?
0
BryceMTAuthor Commented:
For now I am using ADO connection object referencing ODBC DSN...


http://msdn.microsoft.com/en-us/library/ms807027.aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.