Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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=123.12.8.20 (TDAicop1.XYZ.com)," & _
        " 123.12.8.21 (TDAicop2.XYZ.com)," & _
        " 123.12.8.22 (TDAicop3.XYZ.com)," & _
        " 123.12.8.23 (TDAicop4.XYZ.com)," & _
        " 123.12.8.86 (TDAicop5.XYZ.com)," & _
        " 123.12.8.87 (TDAicop6.XYZ.com)," & _
        " 123.12.11.167 (TDAicop7.XYZ.com)," & _
        " 123.12.11.168 (TDAicop8.XYZ.com);" & _
        "UID=xtc487;PWD=Jamaica5"
         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>		<<First	<Prev	Next>	Last>>