How to convert an Access database to SQL?

Question

Hi. I want to convert my Access database to SQL because my host only supports importing a database through SQL in their phpmyadmin.

Answer 1

Hmm, I don't think Access has the ability to generate SQL for creating its tables and data. What I can suggest is importing it into SQL Server (any version should do) and then use SQL Server to generate the create table and data scripts.

Answer 2

Download something like SQL Server 2005 Express, which is free to use, and import it into there, then generate the SQL

http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

Answer 3

Because DB on your server is MySQL, I would:

1. install MySql on local machine
2. connect ODBC to MySQL DB
3. from access open MySQL database (as ODBC resource) and copy data to it
4. export data from MySQL

It's long way round, but it's most likely to work properly.

Answer 4

What version of Access are you using? You will need to export all the Access tables to CSV files and import each one into mySQL. There might be tools that automatically do that now, but they could screw up your data or ay PK and FK mappings you have. Best way IMO to do it table-by-table to ensure data integrity.

Answer 5

I've used Bullzip Access To MySQL on two separate projects and found it to be quite solid and reliable.