CODEWITHSUNDEEP
mysql
usman khalid
usman khalid

Reputation: 75

How to convert the access database file into sql file for imporing in phpmyadmin

I have the MS access database file I have to convert this file into mysql file so I can upload this file in phpmyadmin, I have tried to upload this access file to the phpmyadmin but it is not possible for me to upload, so I want to convert this file into mysql file.How is this possible in ubuntu? If not in ubuntu then how i can convert this file in windows.In windows I have tried SSMA tool to convert access file into mysql file but I am unable to convert this.

Upvotes: 0

Views: 4362

Answers (2)

Parfait
Parfait

Reputation: 107567

First, MS Access is a file level database while MySQL is a server level database. Additionally, an .mdb/.accdb is a binary application file while .sql is a text script file of SQL commands.

In order to migrate from one database to another without third party apps is the following:

  1. Replicate the structures of MS Access tables (i.e., CREATE TABLE...) in MySQL (translating data types accordingly).

  2. Perform an import/export between the two. Assuming you have an MSAccess.exe installed, this can involve exporting MS Access table data to csv, txt, xml formats that can be loaded in MySQL with its LOAD DATA INFILE or LOAD XML commands. Another option is to link the empty MySQL tables (created in #1) inside the MS Access file and run append queries between local and linked tables. If you do not have the Access program, try using code (PHP, Python, Java, R) to connect to the Jet/ACE Engine that underpins the database file. Once connected, use code to query the Access table content and migrate to connected MySQL database or use flatfiles (csv, txt, xml) as the medium.

  3. Once complete, the MySQL data should render in connected phpmyadmin.

**Another point to make is the amorphous definition of MS Access, the program and database engine. It's often a misnomer to think of Access as a database. In actuality it is a GUI console much like phpmyadmin is. The underlying database, the Jet/ACE SQL engine, is only the default connected object which is really a Windows .DLL file and not at all restricted to Access but availabe to any Office or Windows programs. Hence, Access can replace this default for any ODBC/OLEDB compliant RDMS including MySQL. This is why in #2 above you can interact with the table data whether or not you have MSAccess.exe program installed. You just need a PC machine and database ODBC driver.

So MS Access' true comparison is phpmyadmin (for MySQL), management studio (for SQL Server), pgAdmin (for PostGreSQL) with saved views, functions, and interactive forms. The Jet/ACE engine compares to MySQL, SQL Server, PostgreSQL, DB2, and fellow other file-type database, SQLite.

Upvotes: 1

Mikpa
Mikpa

Reputation: 1922

I have used this tool http://www.bullzip.com/products/a2m/info.php to import Access-databases into mysql. It has worked good for me.

Upvotes: 1

Related Questions