Exporting data from MS Access to MySQL

[minckle]

Hi

im trying to automatically transfer some data from a Ms Access Db to a MySQL.

I connect to them both using ODBC and i know the query i want to get the correct data, but where do i go from there.

i want to get field1, field2, field3 in table1 in MS Access to field4, field5, field6 in table2 in MySQL

Does anyone have any tips for me???

 

[hilfy]

There are a couple of things you could use to accomplish this. If you're adding new records, you could use TTables and a TBatchMove component to create the records in your MySQL table.

If you're updating existing data in the MySQL table, you can use a query inside a loop...somthing like this:

SQL: Update MyTable
     Set Field4 = :Field1,
         Field5 = :Field2,
         Field6 = :Field3
     where KeyField = :FromKey

Try
  While not AccessTable.eof do
  begin
    MySQLQuery.ParamByName('Field1').AsString := 
      AccessTable.FieldByName('Field1').AsString;
    MySQLQuery.ParamByName('Field2').AsString := 
      AccessTable.FieldByName('Field2').AsString;
    MySQLQuery.ParamByName('Field3').AsString := 
      AccessTable.FieldByName('Field3').AsString;
    MySQLQuery.ExecSQL;
    AccessTable.Next;
  end;
  Database.Commit;
Except
  on e:Exception do
  begin
    Database.Rollback;
    ShowMessage('Unable to update.  Error=' + e.Message);
end;

When you set up your query, you need to be sure to set the DataType (same type as the field) and the ParamType (ptInput) of the params either in the IDE or in code. The pieces of the query that are preceded by ':' are parameters and Delphi should automatically recognize them as such.

-Dell