Using MyODBC to Connect to
a Remote Database
Connecting to An Established Remote Database
Once you have installed the driver, we will use Access to connect to the remote database. The database must be setup for remote access. At many hosting providers, this is done only by request. Please ask your administrator or hosting provider support team about activating remote access to your MySQL database. Setting up a Data Source, choosing a ODBC driver and creating a Data Source Name (DSN) for Windows applications to reference your database can be a daunting task. There are many settings involved and many terms which you may be unfamiliar with. But broken down, the steps are relatively easy to follow and fall into place.
Click File | Get External Data | Import. Click New in the Data Source window. The MyODBC driver dialog should appear.
Creating a New Data Source
At this point you need to create a new data source. A data source is where data comes from through a ODBC connection. The Database Source Name (DSN) is the name of the database. When you create the database source, give it any name you like.
Using the Create a New Data Source Wizard
The first step is to create a new Windows data source. This allows Windows applications to connect to your ODBC database. Select User Data Source. Click Next.
The next screen presents you with a list of available ODBC drivers you can use to create a data source. (You can have as many data sources as you need, each using any ODBC driver available). Select the MySQL driver. It will be identified as to name, version and the developer, TCX. Click Next.
A purely informational screen appears, displaying the data source type and choice of ODBC driver. As the dialog says, the next step will be to optionally configure the driver-specific settings. Click Next.
The MyODBC driver settings panel should appear. This panel allows you to specify the Windows DSN name, the remote server settings and other compatibility settings. You may give any value for the Windows DSN that is unique (not already used by another data source) to your ODBC sources list. The default port setting should be correct for most situations. Values for Server, User, Password and Port filed do not have to be specified in the ODBC Setup screen. If do specify them here, the values will be used as the default values later when you attempt to make a connection. The values can optionally be changed at that time. (Note: the MySQL manual says "If you specify the option Read options from C:\my.cnf, the groups client and odbc will be read from the `C:\my.cnf' file. You can use all options that are usable by mysql_options()."
A quick reference to the settings.
|Windows DSN||Name of your database used by Windows (this can be anything you like, whatever helps you to remember and recognize the database is fine)|
|MySQL Host||IP address or domain of host database server (obtained from your hosting provider)|
|MySQL Database Name||this is the name of the remote database (obtained from your hosting provider or specified by you when the database was created)|
|User||this is the user name for access to the remote database (obtained from your hosting provider)|
|Password||this is the password used to access the remote database (obtained from your hosting provider; you must have the correct permissions level to do certain operations like creating and dropping tables. Some administrators will give you up to three user id password pairs, one for read only access (SELECT), one for read/write access (SELECT,INSERT,UPDATE), one for administrator level control (CREATE TABLE, DROP TABLE, ALTER TABLE plus all other statements).|
Tip: Tip This tutorial uses Microsoft Access for illustration. To make Access work better with MyODBC, you should consider the following steps.
- You should have a primary key in the table.
- You should have a timestamp in all tables you want to be able to update.
- Only use double float fields. Access fails when comparing with single floats.
- Set the 'Return matching rows' option field when connecting to MySQL.
- Access on NT will report BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change the column to TEXT with ALTER TABLE.
- Access can't always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
- In some cases, Access may generate illegal SQL queries that MySQL can't understand. You can fix this by selecting "Query | SQL Specific | Pass-Through" from the Access menu.
Once the data source has been created, you are back in the Select Data Source dialog. Select the data source you just created from the list. Click OK. That should create a duplicate table on the remote database with all your data. You won't see anything to confirm the data has been transferred. You need to link the table to the remote database.
|Pages: 1 2 3 4 5||Next: Importing and Exporting Tables »|