U.S. Emailing List Home Page

Search from over 700 U.S. Business Niche Databases  

(Example: Accounting, Lawyer, Animal Hospital, Archery, etc..)

 

U.S. Emailing List Home Page

World Database Lists

World Databases Resources

Purchase US Emaling Lists Databases

Contact Us - US Emaling Lists

 

Exporting MS Access to MySQL

 

Microsoft Access is a popular data management application that allows you to store information in tables that it manages directly from the local disk. You can also use Access as a front end, that is, as an interface to information that is located elsewhere and handled by another storage management system. In this case, Access acts as a client that connects to a server that provides the data. The MySQL database system is one such storage manager; if you install the MySQL Connector/ODBC driver (formerly known as MyODBC), Access can make ODBC connections to MySQL servers over the network. You can still use the contents of your tables through Access, but the tables themselves are hosted by the MySQL server.


Access has its strengths, such as an easy to use interface. Access also has its limitations--it's generally used as a personal or single-user application, typically for managing limited amounts of data. (Access is not commonly used for databases hundreds of megabytes in size, for example.) Because of its storage management limitations, you may be considering how to retain the Access interface but migrate your information to a storage manager with greater capabilities. Or you may even be considering a move away from Access entirely. This article outlines some benefits that you stand to gain by using MySQL to manage your data, and provides some guidelines to help you migrate locally stored Access tables to MySQL. The final section of the article lists links to locations where you can find the tools discussed here. Article by

Installing MyODBC Driver

The ODBC API (Open Database Connectivity Application Programming Interface) provides a way for client programs such as MS Access to access databases on remote servers. Before you can use Access with your MySQL server, you must thus first install the MyODBC driver. To do so, follow these instructions:
  1. Download MyODBC 3.51 to your desktop computer, making sure to select the appropriate installation file for your version of Windows. (probably Windows Driver Installer (MSI))
  2. After downloading, double click on mysql-connector-odbc-3.51.12-win32.msi.
  3. When the MySQL Connector/ODBC 3.51 - Setup Wizard window appears, click Next.
  4. Select the "Typical" installation & click Next.
  5. Click Install and wait for the installation to finish. Press Finish.

Congratulations! You have successfully configured MySQL Connector/ODBC to connect to your MySQL server, and you can now link to your MySQL tables with Microsoft Access.

Connecting to a MySQL Database from MS Access; Linking and Editing Tables

Microsoft Access provides two ways to edit the data in your MySQL databases: importing and exporting data, and linking directly to tables. When you import data, you make a copy of a table or query on your local computer; similarly, when you export, you copy information from your local computer back onto your MySQL server. The limitation of this is that you cannot directly edit or overwrite tables in your existing MySQL database. Linking, on the other hand, allows you to make a direct connection to a database on your MySQL server and edit table entries with MS Access. This section shows you how to do just that.

Once you have the MyODBC driver installed, you can connect to your MySQL server and edit database records via a MS Access link. To connect to your MySQL server from MS Access, follow these instructions:

  1. Create a new Access database, or open a pre-existing Access database you want to link to your MySQL server.
  2. Click File -> Get External Data -> Link Tables. This will display the Link dialog box. Find the Files of Type drop-down list at the lower left of the window and click the down arrow. Scroll to the bottom of the list and select ODBC Databases (). The Select Data Source dialog box will appear; it lists the defined data sources for any ODBC drivers installed on your computer. Click on the Machine Data Source tab at the top and click New.
  3. You should now see the Create New Data Source box.
    • Select User Data Source and press Next.
    • Select MySQL ODBC 3.51 Driver and press Next.
    • Click Finish.
  4. You should now see the Connector/ODBC - Add Data Source Name box.
    • In the Data Source Name field, type sample-MySQL.
    • In the Server field, type either ovid.u.washington.edu (staff/faculty/courses/depts sites) or vergil.u.washington.edu (student sites), depending on where you installed MySQL.
    • Leave the MySQL database name field blank.
    • In the User field, type root.
    • In the Password field, type the root password you chose when you installed MySQL.
    • In the Database field, type the name of the database you want to connect to.
    • Click on the Connect Options tab.
    • In the Port field, type the port number you chose when you created your .my.cnf file.
    • Click on the Test button. If it says "Success; connection was made!", then everything should be working. If is says "Request returned with SQL_ERROR" or another error message, click on Diagnostics. The output this gives you may be able to help you figure out what went wrong. You should probably double-check all the settings you entered, and the permissions of the user and database you're using.
    • Click OK.
    • Select "sample-MySQL," the connection that you just made.
    • Click OK again.
  5. You should now be presented with a list of all the tables in your database. Select the tables you would like to view or modify and click OK. Access may ask you to select unique identifiers for some tables; this is only necessary if you will be updating records.
  6. The tables you selected should now appear with globe icons in the tables section of your Access database; double-click a table to view or modify its contents. As long as the table has at least one unique identifier, any changes you make will be updated on the MySQL server as you work.

Note: while you can view table and column properties of linked tables in MS Access, you cannot configure fields, tables, or database schema. phpMyAdmin, however, handles such changes very ably.

References

For more information about MyODBC and using Microsoft Access with MySQL, see MyODBC FAQ: MS Acess Related.

Information provided by UW Technology

 

 

 

 

 

 

 

 

 

 

 

 

 

 
 

Copyright © 2009 businessleadsdata.com - All rights reserved