Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 26 Apr 2006 @ 20:29:56 GMT

  <Prev Next>  
Next> Last>>  

Subj:   With DSN-Less Teradata Connection. How to create a Link in MSAccess
From:   mbmalm

Here is what I'm attempting. (VBA in MSAccess module)

I have a DSN-less connection to Teradata that works fine. I want to create a link interactively. The code I've gleened was originally set up for SQL Server. But seems it should be universal with ADO Connections. Don't know I get an error "-2147467259(800004005) "Invalid Argument" on the statement "adxCat.Tables.Append adxTable".

Is it possible that a Teradata database cannot be linked in this fashion? If it can be linked in this fashion what could be causing the error, or any other suggestions for linking dsn-less connection.

Here is the code:

     '====== Dsnless Connection to Teradata =================

       Dim TDA_conn As ADODB.Connection
       Dim MSAccConn As ADODB.Connection

        Set TDA_conn = New ADODB.Connection
         TDA_conn.CommandTimeout = 0
         DSNdb = "Driver={Teradata};" & _
        "DBCName= (TDAicop1.XYZ.com)," & _
        " (TDAicop2.XYZ.com)," & _
        " (TDAicop3.XYZ.com)," & _
        " (TDAicop4.XYZ.com)," & _
        " (TDAicop5.XYZ.com)," & _
        " (TDAicop6.XYZ.com)," & _
        " (TDAicop7.XYZ.com)," & _
        " (TDAicop8.XYZ.com);" & _
         TDA_conn.Open DSNdb

      ' ------- Current MSAccess DB -------------

         Set MSAccConn = CurrentProject.Connection

         Call NewLinkTable(TDA_conn, MSAccConn, "dTDAv03.vTDA_vtworkforce")

     '========= Link Teradata table to MSAccess =====================

      Private Sub NewLinkTable(ConnSource As ADODB.Connection,
     ConnDestination As ADODB.Connection, _
      dbSourceTable As String)

      Dim adxCat As ADOX.Catalog
      Dim adxTable As ADOX.Table
      Set adxTable = New ADOX.Table
      Set adxCat = New ADOX.Catalog

      Set adxCat.ActiveConnection = ConnDestination

        adxTable.ParentCatalog = adxCat
        adxTable.Name = "link_" & dbSourceTable
        adxTable.Properties("Jet OLEDB:Remote Table Name").Value _
          = dbSourceTable
        adxTable.Properties("Jet OLEDB:Link DataSource").Value _
          = ConnSource.Properties("Data Source Name")
        adxTable.Properties("Jet OLEDB:Create Link").Value = True

      adxCat.Tables.Append adxTable

      End Sub

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020