locked
copy local access table to remote mysql db programmatically RRS feed

  • Question

  • I have a local MS Access database and VB 2010 program.   Within the program, I want to copy a local table with structure and data to a remote MySQL database.  Looking for the best way to do this.

    I know how to make the connection, and I can successfully create and drop tables in the remote, with code like this:

        cmd.CommandText = "DROP TABLE IF EXISTS tsj_test;"
                       
        dt.Load(cmd.ExecuteReader)

       cmd.CommandText = "CREATE TABLE IF NOT EXISTS tsj_test LIKE sourceTables;"

       dt.Load(cmd.ExecuteReader)

    And I know it is possible to programmatically define the table and copy the data row-by-row, but I was hoping to find the elegant solution that would do it all.

    I found some code online like this, which is supposed to copy the data:

      cmd.CommandText = "INSERT tsj_test SELECT * FROM tsj_test IN [Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb;Jet OLEDB:Database Password=]"

    Cant get that to work, and it doesnt do the structure (which I can live without)

    Help much appreciated.  Thanks!


    • Edited by DesignLoverCodeHater Friday, December 27, 2013 3:35 PM
    • Moved by Carl Cai Monday, December 30, 2013 4:56 AM not relate(from vb.net)
    Friday, December 27, 2013 1:43 PM

All replies

  • Hi,

    Welcome to MSDN.

    I am afraid that this is not the proper forum for this issue sicne it is mainly related to the query about Access and MySql.

    For Access issues, you could post them in Access for Developers forum for dedicated support.

    For issues about MySql, I would suggest you post it in MySQL forums for more dedicated support.

    Thanks for your understanding.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 30, 2013 4:55 AM