Project / Support Center
Welcome, Guest. Please login or register. January 16, 2022, 08:08: PM
Home Help Search Login Register
D-Web Web Site Creator D - Web Web Site Creator On-line HTML Editor No Programming knowledge required. Web Global Net PayPal-Cart Shopping Cart System PayPal - Kart Shopping Cart System for E-Commerce over the internet, that's easy to use. Web Global Net Newsletter Manager Newsletter Manager On-line Newsletter Creator with Email Subscriber Management.
Ring Central Discount
Web Global Net Web Application & Web Development Project Center  |  Technical Issues  |  MySQL  |  Topic: Methods of Transferring Databases from Microsoft Access to MySQL 0 Members and 1 Guest are viewing this topic. « previous next »
Pages: [1] Go Down Send this topic Print
Author Topic: Methods of Transferring Databases from Microsoft Access to MySQL  (Read 10105 times)
admin
Guest
« on: April 11, 2007, 09:43: PM »

In general, to migrate information from Access to MySQL, you first copy the contents of your tables from an Access database to the MySQL server. (To perform the operation of transferring the tables to MySQL, you can choose from several methods, described below.) If you plan to continue using Access for the interface to your data, the next step after transferring the tables is to replace them with links: Delete the tables stored in your Access database, establish an ODBC connection from Access to the MySQL server, and recreate the tables as links to the MySQL tables. (Naturally, before you delete anything, it's prudent to make a backup first, just in case something goes wrong.) If you don't plan to continue using Access, you need not create any links.

Some transfer methods require making an ODBC connection to the MySQL server. For this you can use MySQL Connector/ODBC, the MySQL-specific ODBC driver.
Telling Microsoft Access to Export Its Own Tables

One approach to migrating data from Access to MySQL is to use the export feature provided by Access itself to write out the contents of each table as a text file. Each file then can be loaded into MySQL using a LOAD DATA statement or the mysqlimport command-line utility. Suppose you export a table mytable into a file mytable.txt using CSV (comma separated values) format, and you want to import it into a table named mytable in a MySQL database named mydb. You can invoke the mysql program, then issue a LOAD DATA statement to import the file like this:

   C:\> mysql mydb
   mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
       -> INTO TABLE mytable
       -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
       -> LINES TERMINATED BY '\r\n';

Alternatively, use mysqlimport from the command line (type the command all on one line):

   C:\> mysqlimport --local --fields-terminated-by=,
           --fields-enclosed-by='"'
           --lines-terminated-by='\r\n'
           mydb mytable.txt

If you need to provide connection parameters such as the hostname, user name, or password, list them on the mysql or mysqlimport command line before the database name:

   C:\> mysqlimport --local --fields-terminated-by=,
           --fields-enclosed-by='"'
           --lines-terminated-by='\r\n'
           -h some_host -p -u some_user
           mydb mytable.txt

The advantage of this approach is that it requires no special conversion tools. It can be used to produce data files even on machines that have no MySQL support. (If you don't have the MySQL client programs installed on your Access machine, create the data files, then copy them to another machine where the MySQL programs are installed and load the files into MySQL from there.) The disadvantage is that the MySQL tables must already exist before you can load data into them, so you must issue the appropriate CREATE TABLE statements yourself. For the example just shown, that means you must already have created the table mytable in the mydb database before using either LOAD DATA or mysqlimport.
Converters That Generate Intermediate Files

A second approach to data transfer is to use a converter that reads an Access table and produces from it one or more files containing SQL statements that create the table for you and load data into it. Then you execute the intermediate SQL file or files using the mysql program. Several free converters that work like this are available, each of which takes the form of an Access module:

    * exportsql.txt
      Works with Access95, Access97, Access2000. Exports all tables in a database, producing one file containing DROP TABLE statements (in case you want to remove MySQL tables created during an earlier data transfer exercise) and another file containing CREATE TABLE and INSERT statements for creating and loading the tables. The files are written to the C:\TEMP directory.
    * access_to_mysql.txt
      Exports all tables in a database into a file C:\TEMP\mysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any existing MySQL tables and recreate them. Less sophisticated than exportsql.txt in terms of type conversion and handling of special characters.
    * mdb2sql.bas
      Access97 only. Exports selected tables to files in a directory of your choosing. Writes a data file for each selected table, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the data files into them.

Near the beginning of the source code for each of these converters, you'll find instructions that you should read, because the details of the process for generating the SQL and data files are converter-specific. Also, be sure to note any prerequisites that must be satisfied before using the converters. These include the following:

    * Both exportsql.txt and access_to_mysql.txt expect to write files to the C:\TEMP directory, so you must create that directory if it doesn't exist:

         C:\> mkdir C:\TEMP

      Alternatively, you can modify the module source so that it writes files to another existing directory.
    * If you want to use exportsql.txt to convert Access2000 tables, you need to enable support for the DAO (Data Access Objects) interface. From Access, go into the Visual Basic editor, select the Tools >> References menu option, then enable the "Microsoft DAO 3.6 Object Library" option in the window that comes up.
    * mdb2sql.bas requires that you have Advanced Wizards installed, because it uses the Documenter function included in that Wizard set.

After following the export procedure for a converter that generates intermediate SQL files from Access tables, you'll end up with one or more files that need to be executed with the mysql program, as follows. Assuming that you want to create tables in a database named mydb, you can execute a SQL file file.sql like this:

   C:\> mysql mydb < file.sql

If you need to provide connection parameters, list them on the command line before the database name:

   C:\> mysql -h some_host -p -u some_user mydb < file.sql

Converters That Perform Direct Data Transfer

Some conversion tools can transfer data directly from an Access database into MySQL. That is, they create the MySQL tables for you and load the information into them as well. This avoids the need for any intermediate files. On the other hand, such tools require that you be able to connect to the MySQL server from the machine on which your Access information is stored. (This requirement is easily satisfied if you install MySQL on your Access machine.)

Tools that can perform direct data transfer are:

    * MyAccess
      $30 shareware. (Non-registered copies are fully functional, but an annoyance dialog that must be dismissed pops up every five minutes.) Works with Access97, Access2000. MyAccess is an Access add-in that allows direct transfer when you connect from Access to MySQL over an ODBC connection.
    * DBTools
      Free. Works with Access97, Access2000. DBTools actually is intended primarily as an application for administering MySQL, but it includes data import capabilities that can be used to read Access databases for transfer to MySQL. (It can also read data from other sources such as Excel spreadsheets, making it particularly useful for transferring to MySQL information that is stored in a variety of formats.) Because DBTools reads Access databases directly, you can use it to migrate Access tables even if you don't have Access installed locally, as long as you have the database files containing the tables to be transferred. DBTools does not require ODBC.
    * MySQLFront
      Free. MySQLFront is similar in many ways to DBTools. It can read Access97 and Access2000 files directly. If ODBC is installed, MySQLFront can import information into MySQL from ODBC data sources over the network. (Unfortunately, MySQLFront development has ceased and it is no longer distributed by its author. However, you may be able to find it on alternate download sites by using a search engine.)

As an example how one of these tools works, here's how you'd use DBTools to perform data transfer from Access to MySQL. Begin by visiting the DBTools download page at http://www.dbtools.com.br/, transferring the installer (a program named setup.exe), and running it. This will install DBTools on your machine.

If you want to transfer Access2000 databases, you need to enable DAO. (If you don't, DBTools will crash whenever you try to open an Access2000 database.) To turn on DAO, launch DBTools (it will tell you there is no server profile; that's normal), select the Options >> Preferences menu item, and select the DAO 3.6 option. Then quit and relaunch DBTools, because DAO isn't actually activated until the next launch after you enable it.

With DBTools running, establish a connection to your MySQL server. (Click the Server icon in the toolbar or use the Server >> Add Server menu item to define a profile for the MySQL server you want to connect to.) You must be connected to the server before you can transfer information; many of the menu items and icons in the tool bar are disabled until you establish a connection, including those related to importing data.

After connecting to MySQL, use the Import Data Wizard to select the Access database file containing the tables you want to transfer. One of the dialogs presented during this process asks you to select the file type for the kind of database you want to use. Select the Access97 type for either Access97 or Access2000 databases.

If you intend to continue using Access after transferring the tables, open the database from Access, delete the tables that you just transferred to MySQL, connect to the MySQL server, and set up links to the tables.
Report to moderator   Logged
Pages: [1] Go Up Send this topic Print 
Web Global Net Web Application & Web Development Project Center  |  Technical Issues  |  MySQL  |  Topic: Methods of Transferring Databases from Microsoft Access to MySQL « previous next »
Jump to:  


Login with username, password and session length
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!