How To Convert Access to MySQL

Both Microsoft Access and MySQL have found wide use in many companies though for different reasons. The main advantage of Access is its relative ease of use while MySQL’s strength comes from its versatility. MySQL’s versatility allows it to work in conjunction with programs written in various languages as well as even Access. It allows multiple-user access, makes management of large databases easier, provides increased security, and simplifies backup management. In addition MySQL is provided free of charge. To migrate from an Access to MySQL we will be using a free program called DBTools. DBTools has a feature that allows us to directly import data from Access files.
 

1. Install and configure MySQL or obtain the information need to access  pre-existing server.
2. Visit DBTools Software and download the installer for the demo of  DBTools QueryIT.
3. If you are in Access 2000, go to Tools > References menu option and click the "Microsoft DAO 3.6 Object Library" option in the dialogue box.
4. Enable DAO (Data Access Objects) by launching DBtools, select Options > Preferences and choose the DAO 3.6 option. It is not important for you to know what DAO are. Just know that, if this is not done, the program will crash.
5. Quit and relaunch DBTools.
6. Now we will establish a connection to your MySQL server. Either click the server icon on the toolbar or go to Server > Add Server to create a profile for your new connection.
7. After establishing a connection, use the Import Data Wizard to browse for the Access file you want to use.
8. Select the version of Access the file was created in, as prompted.
9. If you would like to use Access as a front end, open the database from Access and remove the tables you transferred. Using Access as a front end simply means using Access but having the data stored in a MySQL table.
10. Download the MySQL Connector ODBC (Open Database Connection) driver from the MySQL website. An ODBC allows you to create a connection between two or more databases.
11. Run the installer to install the driver.
12. Now there should be a new icon in your Control Panel that reads either ODBC or Data Sources. Double click this to open the ODBC Data Source Administrator.
13. With the "User DSN" tab selected, click on "Add." The DSN contains data source information. This will allow you to create a new data source and input information necessary for the driver to communicate with the data source.
14. Select "MySQL ODBC 3.51 Driver" from the list of drivers, and click "Finish."
15. Fill in the form and create a data source name that you will use to refer to the database when you wish to access it.
16. Click "Test Data Source" to ensure you have entered your login information correctly.
17. Click OK to create the DSN.
18. Click File > Get External Data.

19. You will then be presented with a file browser. As before, choose "ODBC Databases ()" from the drop-down menu at the bottom, and choose your DSN from the list of Machine Data Sources. You will then see a list of all the tables in your MySQL database. Select the table that you want to link, and click OK.

20. It will then prompt you to select a unique record identifier. A unique record identifier is the cell whose value makes a record unique. In your Access database you may have an ID number for every record — unique to that record. Doing this is very important.

21. You can now access your linked MySQL database through Access. This means that the data for your database is now stored in MySQL tables. If you would like, you could use Access as the front end and continue to use it as you did prior to the migration. The only difference is that the data would no longer be stored in the Access file, but rather in MySQL tables.