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!
