Update Server Data with MS Excel or MS Access

Summary

This article demonstrates how to connect to a MySQL server and edit database records using Microsoft Excel. Since it is hard to link Excel and MySQL directly, we'll complete the task via MS Access. It assumes that you can installed MySQL, MS Office on your computer.

Note: The procedures outlined in this article were written using Microsoft Office 2003 on Windows XP. They may have to be adjusted slightly if you are using another version of Microsoft Office or Windows.

Included on this page:

• Installing the MyODBC Driver
• Create an Access Database From MySQL
• Link and Export the Table from the Server
• Update the Server Data from the Excel File
• References

Installing the 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.

Create an Access Database From MySQL

If you have done this before, you can skip it and go directly to the next topic

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

   

3. Click New.

   

4. 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.
5. You should now see the Connector/ODBC - Add Data Source Name box.
   • In the Data Source Name field, type sample-mysql2.
   • In the Server field, type either flatfile0.ait.iastate.edu
   • Leave the MySQL database name field blank.
   • In the User field, type stat.
   • 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. In this example, we use stat
   • Click on the Connect Options tab.

     

   • In the Port field, type the port number.
   • 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.

     

Link and Export the table from the server

1. Do step 1-2 in the topic above
2. Click on the Data Source Name to select it, in our example, it is sample-mysql2 as we created before

   

3. You should now be presented with a list of all the tables in your database. Select the tables (here we use testing as an example) 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.

   

4. 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.

   

5. To export the data, click File->Export , choose the .xls format and type the file name and click Export All

Update the Server Data from the Excel File

1. Do step 1-4 in the topic above to open the table you want to update
2. Click on the up-right corner (the grey cell on the left of the first column name) of the table, you'll see all record fields will be in black background. Right click your mouse and choose Delete Record

   

3. Close the window of table
4. Click File->Get External Data->Import, find the Excel file which containning the new data, and click Import

    

5. Click Next->Next

    

6. Choose In an Existing Table: and from the list on the right side, find the table on server to update

   

7. Click Next->Finish->OK

   

References

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