ODBC - Connection MySQL to an Access Database
MyODBC is the Open Database Connectivity (ODBC) compliant driver that is used to connect to a MySQL database. Here's how MySQL.com describes the MyODBC driver:
"MySQL provides support for ODBC by means of [the] MyODBC program. MyODBC is a 32-bit ODBC (2.50) level driver for connecting an ODBC-aware application to MySQL.
In simpler terms, MyODBC is a free tool to allow a number of common desktop-based databases to connect to a server-based MySQL database. This means you can build a database on your PC using the db tool you're already familiar with, and then "pour" the structure and data into your web site database. From there you can use most any programming language - Perl, PHP, ASP, JSP, or Cold Fusion - to create a powerful database-driven web site.
MyODBC is a free download from MySQL.com and is easy to install. First go to http://www.mysql.com/downloads/api-myodbc.html.
We strongly recommend that you select the latest "Stable" release version for your particular operating system. Click
on the link that matches your operating system. This will take you to the mirrors page - click on the link that's closest to you and save the download to a folder on your hard drive.
Once complete, extract the zip file to a temporary directory and run the included setup.exe file. This will install the MyODBC driver. When the installation is complete, simply
click on the close button to bypass the data sources configuration dialog. Click the OK button and you're done! Configure the MySQL driver for use.
Windows 2000
From the Start menu, select Settings then Control Panel. Double
click Administrative Tools and then double click Data Sources (ODBC). Windows 95/98/NT
From the Start menu, select Settings then Control Panel. Double-click
the ODBC Sources icon.
Select the first tab on the next screen, "User DSN" and then click the add button on the right side. By clicking the add button you
will be given a choice of drivers you can set up for a data source. You should find MySQL in
the list. Select MySQL and click finish. The TDX mysql driver default configuration screen will then appear. You will want to fill
out the fields with the appropriate information (all other fields and checkboxes can be left blank).
Windows DNS Name
Type a name for this particular driver that you will be using for MySQL. The name is
something of your choosing - each DNS name used must be unique and a single word Server
This is your domain name, without the www (must be domain name, not IP) MySQL Database Name
The name of your MySQL database (usually yourdomain_com)
User
The username for the MySQL database you will be connecting to (usually the same as your account username). Password
The password for the MySQL user in the field above (usually the same as your account password). Port
Leave blank for default (3306)
Exporting an MS Access Database to MySQL
Using the Windows MyODBC drivers listed above you can export Microsoft Access databases from your PC
directly to your MySQL database on your server.
The very first step is the only one for which you need our help. Your MySQL database has to be
enabled for "remote access". All you need do is open a support ticket -
and
request "ODBC Remote Access" for your database. We'll send you a confirmation email when that process is complete, and
you can proceed to the next step.
Open up MS Access and create or select the database you want to move to your server in
the Tables section. Once you have selected the appropriate table, select Export...
under the File menu. The Save Table screen will appear. You will want to change the Save as type
to ODBC Databases and click Export. The Export screen should appear. The Export Addresses to: field should be
the name you want to call this specific table on the server. You should now see the Select Data Source screen. Select the Machine Data
Source tab and then select the DSN you configured in step 4a above. The table should then be moved to your host account database under the user you
specified for MySQL. To verify this you can take a look at the PHPMyAdmin browser-based interface for your database, from your account Control Panel.
Importing a MySQL Database to MS Access
It is also possible to import MySQL databases from your host account to a MS Access
database on your PC. In the example below, you can link a table from your site database to your Access database.
Open Access. In your current database, selected the File pulldown menu, then Get External Data >>> Link Tables This will bring up the Select Data Source screen. Select the Machine Data
Source tab and choose the appropriate Data Source Name that you setup
previously. Select the table(s) you
want and click OK.
|
