— layout: post title: Setting up an ODBC connection in Windows
created: 1291233660 categories: [] —
Installing the MySQL ODBC driver (do this step once) {style=”font-weight: bold; font-style: inherit; font-size: 15px; font-family: ‘Segoe UI’, ‘Lucida Grande’, Arial, sans-serif; vertical-align: baseline; line-height: 1.25em; color: #444444; padding: 0px; margin: 0px; border: 0px initial initial;”}
- Download the appropriate MyODBC driver
from http://dev.mysql.com/downloads/connector/odbc/5.1.html
- Run the file as an administrator
- Follow the prompts to install the driver. You will just use the
defaults so just keep clicking next/OK until the installation is
finished
Setting up Windows to access a database on Serenity (do this step once for each database) {style=”font-weight: bold; font-style: inherit; font-size: 15px; font-family: ‘Segoe UI’, ‘Lucida Grande’, Arial, sans-serif; vertical-align: baseline; line-height: 1.25em; color: #444444; padding: 0px; margin: 0px; border: 0px initial initial;”}
- Open the Control Panel
- Open the Administrative Tools
- If you are using the Classic View the Administrative
Tools link should be in the Control Panel window
- If you are using the Category View first open Performance
and Maintenancethe open Administrative Tools
- Open Data Sources (ODBC) using your administrative account by
right clicking on the icon and selecting Run As
- Click the The following user radio button
- Enter your admin username and password
- Click OK
- Select the System DSN tab
- Click Add
- Select the MySQL ODBC Driver
- Click Finish
- Enter a name for the remotely accessed database in Data Source
Name (e.g., BBS_on_Serenity)
- Enter a description of the database if it will be helpful to you
- In the Server box enter the name of the server
- In the User box enter your MySql username
- In the Password box enter your MySql password
- Click on the arrow at the right hand side of the Database box
and wait patiently for a few seconds
- You will see a list of the databases on Serenity that you have
access to
- Click on the database you want and press OK twice
Accessing the data in Access 2007 {style=”font-weight: bold; font-style: inherit; font-size: 15px; font-family: ‘Segoe UI’, ‘Lucida Grande’, Arial, sans-serif; vertical-align: baseline; line-height: 1.25em; color: #444444; padding: 0px; margin: 0px; border: 0px initial initial;”}
- Open the Access database that you want to have access (alright, now
it’s just annoying) to the MySQL database you’ve setup using ODBC
(or start a new database)
- Select the External Data tab on the Ribbon(the tabbed
toolbar at the top of the screen)
- In the Import section select More and then ODBC Database
- Click the Link to the data source by creating a linked
table radio button (this will mean that you will always be working
with the most up to date data and that if you have editing
priveldges that you will be able to insert or modify data on the
server.
- Click OK
- Select the Machine Data Source tab
- Select the Data Source Name for the ODBC data source you
created (if the data source you setup is not present see below for
an alternative approach)
- Click OK
- In the list of Tables select the tables you wish to include in
your Access database and click OK
- The tables will appear in their own group and the fact that they are
linked from the server will be indicated by a picture of the earth
next to the table name.
- You can now treat this like any other table in your database.
- If you want to remove the link, just right click on the table and
select Delete
Setting up an ODBC source directly in Access 2007 {style=”font-weight: bold; font-style: inherit; font-size: 15px; font-family: ‘Segoe UI’, ‘Lucida Grande’, Arial, sans-serif; vertical-align: baseline; line-height: 1.25em; color: #444444; padding: 0px; margin: 0px; border: 0px initial initial;”}
- If Access does not display the desired database after following
Steps 1-16 of “Setting up Windows…”
- Simply follow steps 1-6 of “Accessing the data…”, then
click New
- Select System Data Source and hit Next.
- Follow steps 6-16 of “Setting up Windows…”
Source: This started as a modification/simplification of
the walkthrough at devshed by W.J.
Gilmore, which has
been generously modified and added to by the members of
weecology.