Any access
table can be transferred ( or exported ) to MySQL database easily but using MyODBC. We will work
on the transfermation process and hope that you are already runing MySQL
database and your access table is ready. Any Excel table can be opened by Access and then data can be posted to MySQL
To transfer
access table to mysql we have to install MyODBC.
.
MyODBC 3.51
is a 32-bit ODBC Driver, also known as MySQL ODBC 3.51 Driver. This is a
enhanced version to the existing MyODBC 2.50, with ODBC 3.5x
specification level 1(complete core API + level 2 features) in order to
continue to provide all functionality of ODBC to access MySQL.
You can download MyODBC from
http://dev.mysql.com/downloads/connector/odbc/3.51.html
You need to configure MyODBC to connect to MySQL db. After
installing MyODBC you can go to administrative tools and open Data Sources
(ODBC).
Go to ODBC Data Source Administrator and select myodbc
Click the configure button to enter the MySQL database
details. This is required to configure myodbc to convert access to MySQL.
Here you can test the data source for connection etc.
Click OK to save the configuration.
Now let us go to Access. Open access database you want to
transfer to MySQL. Select the table we will be exporting and on file menu select
export.
We need to select the destination and for this select ODBC
in save as type
Once save as type ODBC is select , the system will prompt
for the table name we want to transfer.
This will be the destination table name and can be changed
to any name. Here we have to take care that if at configuration of myodbc if it
is set to overwrite any existing table then any duplicate name will overwrite
the old table. Once we give the table name and click OK system will show all the
ODBC available and we will select myodbc in select data source.
If we have not configured myodbc before with myslq login
details like user id, password, db name, host then here we will get prompt
to enter the details. If all are entered properly then one new table will be
created in specified database ( as per the configuration process ) with the
records.
| 