Strange One - Online MySQL -> offline MS Access

OrionSystem

Just trying to convert an online MySQL database to an offline MS Access one to do a mail merge.

At the moment i have the idea of running a PHP script to generate the required SQL statements and then trying to run the SQL statements locally on the Access database... its a nightmare.

Seemed straight forward...

Any ideas ????

Enygma

Have you got phpmyadmin? (phpmyadmin.net)
You can dump the database into a text file containing all the SQL queries that are needed to recreate the database. Kinda what you were talking about.

There's probably other ways to do it but I know you can use that.

OrionSystem

i already have all the SQL statements needed to re create the database... wrote a php script to make that. i just need to input them directly into Access... as in put INSERT statements in Access... all i can find are SELECT statement input forms... tryed just putting INSERT but to no avail.

Evil Phil

I don't have Access here with me but isn't there Import External Data functionality within Ms Access? It will allow you to import tables with data from an external data source if memory serves ...

davej

Yes the way to do it is to export your mysql tables / data into csv format using the

"SELECT * INTO OUTFILE "filename" fields terminated by ',' lines terminated by "\n" FROM table"

statement and then import them into the other database.

You don't need to write a php script yourself to recreate all of the select statements necessary to build the database: mysqldump can do this for you.

<edit> mixed up mysqldump</edit>

davej

bonkey

Originally posted by Evil Phil
I don't have Access here with me but isn't there Import External Data functionality within Ms Access? It will allow you to import tables with data from an external data source if memory serves ...

Yup.

You set up an ODBC connection to the database of your choice (assuming MySQL has an ODBC driver). Then in Access you tell it to attach (or import, can't remember) external data, connect to the tables, and there you go.

This *may* lose some stuff like default values, relational integrity, etc. but they should be trivial to recreate.

Another option is to use "Data Transformation Services" if you have a copy of MSSQL 7.0 or newer lieing about. There is no requirement for either source or destination to be an MSSQL database!

jc

d4r3n

www.accessmysql.com its a mysql plugin for ms access, you connect in to the mysql server and simply download the table or whatever.

OrionSystem

thanks man, thats brilliant

exactly what i needed....