📄 mysql-access-3.5.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd"><html><head><title>Using Microsoft Access with MySQL</title>
<link rel="stylesheet" href="mysql-access-3.5_files/home.css" type="text/css"></head>
<body bgcolor="#ffffff">
<a href="http://www.washington.edu/"><img id="toplogo" src="mysql-access-3.5_files/UWlogo150p.gif" alt="University of Washington" border="0"></a>
<div id="toolbar">
<span class="l1text"><a href="http://www.washington.edu/home/search.html">Search</a> |
<a href="http://www.washington.edu/home/directories.html">Directories</a> |
<a href="http://www.lib.washington.edu/research/">Reference
Tools</a></span></div>
<!--Section Banner Table -->
<div class="bannerwrapper">
<div id="topbanner"><img src="mysql-access-3.5_files/rib0.gif" alt="" height="16" width="6"></div>
</div>
<div id="crumbs"><span class="l1text"><a href="http://www.washington.edu/">UW Home</a> > <a href="http://www.washington.edu/uwin/">UWIN</a> > <a href="http://www.washington.edu/computing/">Computing and Networking</a> > <a href="http://www.washington.edu/computing/web/">Web</a> > <a href="http://www.washington.edu/computing/web/publishing/">Web Publishing</a> > <a href="http://www.washington.edu/computing/web/publishing/mysql.html">Using MySQL</a></span> </div><div class="forceclear"></div>
<table style="table-layout: fixed;" align="right" bgcolor="#ffffcc" border="1" cellpadding="5" cellspacing="0">
<tbody><tr>
<td width="120">
<p>This article applies to the following UW Web servers:</p>
<center>
<a href="http://faculty.washington.edu/">faculty</a><br>
<a href="http://staff.washington.edu/">staff</a><br>
<a href="http://students.washington.edu/">students</a><br>
<a href="http://courses.washington.edu/">courses</a><br>
<a href="http://depts.washington.edu/">depts</a><br>
</center>
</td>
</tr>
</tbody></table>
<h1>Using Microsoft Access with MySQL</h1>
<h4>Summary</h4>
<p>This article demonstrates how to connect to a MySQL server and edit
database records using Microsoft Access. It assumes that you have
familiarity with MS Access and that you have already <a href="http://www.washington.edu/computing/web/publishing/mysql-install.html">installed MySQL on the UW servers</a>.</p>
<p><b>Note:</b> The procedures outlined in this article were written using Microsoft Access 2003 on Windows XP.
They may have to be adjusted slightly if you are using another version of Microsoft Access or Windows.</p>
<p><i>Included on this page:</i></p>
<ul>
<li><a href="#1">Installing the MyODBC Driver</a>
</li><li><a href="#2">Connecting to a MySQL Database from MS Access; Linking and Editing Tables</a>
</li><li><a href="#3">References</a>
</li></ul>
<h4><a name="1">Installing the MyODBC Driver</a></h4>
<p>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:</p>
<ol>
<li> <p>Download <a href="http://dev.mysql.com/downloads/connector/odbc/3.51.html">MyODBC 3.51</a>
to your desktop computer, making sure to select the appropriate
installation file for your version of Windows. (probably Windows Driver
Installer (MSI))</p>
</li><li> <p>After downloading, double click on <b>mysql-connector-odbc-3.51.12-win32.msi</b>.</p>
</li><li> <p>When the <b>MySQL Connector/ODBC 3.51 - Setup Wizard</b> window appears, click <b>Next</b>.</p>
</li><li> <p>Select the "Typical" installation & click <b>Next</b>.</p>
</li><li> <p>Click <b>Install</b> and wait for the installation to finish. Press <b>Finish</b>.</p>
</li></ol>
<p>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.</p>
<h4><a name="2">Connecting to a MySQL Database from MS Access; Linking and Editing Tables</a></h4>
<p>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.</p>
<p>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:</p>
<ol>
<li><p>Create a new Access database, or open a pre-existing Access database you want to link to your MySQL server.</p>
</li><li><p>Click <b>File -> Get External Data -> Link Tables</b>. This will display the <b>Link</b> dialog box.
Find the <b>Files of Type</b> drop-down list at the lower left of the window and click the down arrow.
Scroll to the bottom of the list and select <b>ODBC Databases ()</b>. The <b>Select Data Source</b> dialog box will appear; it lists the defined data sources for any ODBC drivers installed on your computer.
Click on the <b>Machine Data Source</b> tab at the top and click <b>New</b>.</p>
</li><li><p>You should now see the <b>Create New Data Source</b> box.
</p><ul>
<li>Select <b>User Data Source</b> and press <b>Next</b>.
</li><li>Select <b>MySQL ODBC 3.51 Driver</b> and press <b>Next</b>.
</li><li>Click <b>Finish</b>.
</li></ul>
</li><li><p>You should now see the <b>Connector/ODBC - Add Data Source Name</b>
box.
</p><ul>
<li>
<!-- Here I stopped with this new version, because I couldn't get it to work-->
<ul>
<li>In the <b>Data Source Name</b> field, type <tt>sample-MySQL</tt>.</li>
<li>In the <b>Server</b> field, type either <tt>ovid.u.washington.edu</tt> (staff/faculty/courses/depts sites) or <tt>vergil.u.washington.edu</tt> (student sites), depending on where you installed MySQL.</li>
<li>Leave the <b>MySQL database name</b> field blank.</li>
<li>In the <b>User</b> field, type <tt>root</tt>.</li>
<li>In the <b>Password</b> field, type the root password you chose when you installed MySQL.</li>
<li>In the <b>Database</b> field, type the name of the database you want to connect to.</li>
<li>Click on the <b>Connect Options</b> tab.
</li><li>In the <b>Port</b> field, type the port number you chose when you <a href="http://www.washington.edu/computing/web/publishing/mysql-install.html#step9">created your <tt>.my.cnf</tt> file</a>.</li>
<li>Click on the <b>Test</b> 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
<b>Diagnostics</b>. 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.
</li><li>Click <b>OK</b>.</li>
<li>Select "sample-MySQL," the connection that you just made.</li>
<li>Click <b>OK</b> again.</li>
</ul>
</li><li><p>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 <b>OK</b>.
Access may ask you to select unique identifiers for some tables; this is only necessary if you will be updating records.</p>
</li><li><p>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.</p>
</li></ul></li></ol>
<p><b>Note</b>: while you can view table and column properties of linked tables in MS Access, you cannot configure fields,
tables, or database schema. <a href="http://www.washington.edu/computing/web/publishing/phpmyadmin.html">phpMyAdmin</a>, however, handles such changes very ably.</p>
<h4><a name="3">References</a></h4>
<p><a name="3">For more information about MyODBC and using Microsoft Access with MySQL,
see </a><a href="http://dev.mysql.com/doc/connector/odbc/en/faq_4.html">MyODBC FAQ: MS Acess Related</a>.</p>
<div id="footer"><div id="footerseal">
<a href="http://www.washington.edu/"><img src="mysql-access-3.5_files/footersealW.gif" alt="UW Seal"></a>
</div>
<div id="addressright"><address>
A service provided by<br>
<a href="http://depts.washington.edu/cac/"><img src="mysql-access-3.5_files/CnC_full_UW_Purple-1.gif" alt="Computing & Communications" border="0" height="14" width="202"></a>
</address>
</div>
<div id="address"><address>
<a href="http://depts.washington.edu/cac/">Computing
& Communications</a><br>
help@cac.washington.edu<br>
Modified: October 28, 2005
</address>
</div>
</div>
<!--Created by chtml on Sep 15, 2006 11:36am--></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -