Using MyODBC to Connect to a Remote Database - MyODBC Setup
(Page 4 of 6 )
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()."
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.
Importing and Exporting Tables Importing a Table From the Remote Database Open the Access database. On the database window (the one with the tabs), select the tables pane. Go to File, then Get External Data. Choose Import from the menu.
In the dialog, select the choice "To an External File or Database." The SaveAs dialog will appear. In the Save as type select box, choose ODBC Databases (). Click Import.
The Import Objects dialog will appear. If the table you wish to import is listed, select it. Click OK.
Access will begin importing the table by querying the remote database through the ODBC connection. This connection can be slow for a large table, so have a cup of coffee while Access does its work.
Exporting a Table to the Remote Database Open the Access database. On the database window (the one with the tabs), select the tables pane. Select the table to export. Go to File, then Get External Data.
The Export dialog will appear. If the table name is what you want, click OK. The Select Data Source dialog appears. Select the Machine Data Source tab.
We will create a new data source just for your this database. See the previous section on creating a new data source. This will make it easy to connect again to the database. Click New. The Create New Data Source appears. Select User Data Source. Click Next. Select the MySQL driver from the list. Click Next. The panel will display your driver info. Click Finish.
Next: Exporting a Table to the Remote Database (contd.) >>
More MySQL Articles
More By Steve Knoblock
