exporting from access to MySQL

[jamesmor]

I have both a MySQL 5.1 backend and a MSSQL (don't know its version) backend that I connect to from an Access 2003 frontend.

I'm trying to export data from MSSQL to MySQL. Now I don't have direct access to the MSSQL database so I've got a query pulling the data into Access.Then I'm trying to export from an Access table to a table that I have created in MySQL. To do this I'm using the following line:

DoCmd.TransferDatabase acExport, "ODBC", "odbc;DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=compiledfile;USER=root;PASSWORD=Jamiem01;OPTION=3;", acTable, "Registrants", "registration", False

Every time I try to do this Access crashes, no error messages come up at all. Can anyone take a look at this and see what I might be doing wrong?

Any help is greatly appreciated.

Thanks

 

[ChrisLomax]

To be honest i wouldnt use that command on a MySQL database, i would use a temporary table in Access and simply append all the info from the MySQL database to the temporary table?

That way you are only running a query and you know queries work on MySQL databases!

It is basically doing the same as what you are doing now except in a query.

 

[jamesmor]

Figured it out with much research. Looks like when Microsoft updated their Jet drivers, they broke connectivity with MySQL, I couldn't even link tables, or get Access to connect to MySQL at all. Below is a link that show's the issue for anyone else.

http://bugs.mysql.com/bug.php?id=9932