Vba - Multiple Data Sources

  • Hi


    First post so please be gentle!


    I am a SQL/VBA developer in sunny England, and I am currently working with SQL 2000 / Access 2000 and Teradata SQL (DW solution of choice at work)


    We use our SQL Server as a tactical solution to get the business what it wants quickly, and the Teradata platform for long term projects and requirements


    What I am trying to achieve is to be able to get data from SQL Server (or any source really, Access, Excel, Text etc) and import into Teradata, or vice versa.


    I am able to connect to both (copies of code below), however I am struggling with the CONNECTION.EXECUTE sub at the bottom - How do I explain to the script that I want to insert rows from one connection into the other?
    (Very similar to a Data Transformation in SQL Server)


    We want to use Access and VBA as the interface to save involving IT, licences and all the usual red tape involved getting software installed, as everybody has Access!


    Spent the last 4 hours trying, can't seem to find anything on the web!


    Part 1 - Connections Strings (names removed to protect the innocent...)
    ______________________________________________________________



    Part 2 - The insert statement I can't resolve...
    __________________________________________________________

    Code
    1. Sub InsertRows()
    2. OpenTeradataConnection
    3. OpenSQLConnection
    4. TDconn.Execute "INSERT TeradataTable.MyTable SELECT * FROM SQLConn.myTable"
    5. End Sub




    Ta!


    Simon

  • Re: Vba - Multiple Data Sources


    Same here with method - However this time we are dealing with millions of rows so very inefficient to do the looping statement inside the two recordsets


    There must be a way to bulk insert?