Using MyODBC to Connect to a Remote Database - Exporting a Table to the Remote Database (contd.) (Page 5 of 6 )
See the previous instructions for setting up the MyODBC driver. The MyODBC driver dialog should appear.
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.
To link the table, select it in the table pane. Go to File | Get External Data | Link Tables. In the Link dialog, select from the Files of type list ODBC Databases (). The Data Source Dialog will appear. From the Machine Data Source tab, select your alumni database source name again. A series of dialogs will come up. One may ask you what tables you want to link, select the alumni table. Another may ask you what column you want to uniquely identify records, use the record ID column (usually the PRIMARY KEY) if you have one.
Now, a second table will appear in the panel with the same name, but a "world" icon showing that it is remote. You can work with this table like any other, creating a report based on it or a form that you can use to update the remote table.
It may be necessary to change some of the column names in the table you are going to export. MySQL reserves certain words for its own use. Most of the SQL keywords are reserved. For example UPDATE is a reserved word, so you can't use it as a column name. Except that function names do not clash with table or column names. For example, ABS is a valid column name. I am not completely sure about this and would appreciate any observations you have.
Open a blank Access database, go to File | Get External Data | Link Tables | Select from files of type ODBC, select Machine Data Sources tab, select your database name from the list, click OK.
A Link Tables dialog should come up. You should see a list of table names. Select the table you wish to link to. Click OK.
A Select Unique Identifier dialog should come up. You should see a list, select the column that you want to represent the primary key (or columns if more than one column is required to uniquely identify a row). Click OK.
You should see a remote table in the Tables tab (the globe indicates a table linked to a remote data source). Open the table, you should see the data in your remote table.
Microsoft Access 2000 has a bug affecting the export of tables to a remote database. Table names are not sent in the SQL query when a database is exported. If you just go to File | Export | Select ODBC, choose MyODBC an ODBC error will occur. There is a hotfix available for this bug, thanks to the diligence of the MySQL community and responsiveness of Microsoft. The only word I have on this issue is from user group member who communicated with a member of the Microsoft Jet development team.
They replied "We have found the problem with Access 2000 and MySQL/MyODBC. The problem is indeed on the Access side of ODBC, not the driver side. The Jet team has made a fix and will release with the next scheduled update. Thank you for bringing this issue to our attention. Jim Sturms. Access Program Manager." At the time of writing a hotfix was available through Microsoft's beta test program. However, linking to existing tables in the database is unaffected. Someone using Access 97/95 can connect to the database and export the data. Once the tables are online, you should be able to link to the table and modify it as needed.
MyODBC Compatibility and Options
ODBC Compatibility Here are a few notes on making your data compatible with ODBC. They are from the MySQL manual.
The "zero" values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values "0" or 0, which are easier to write. "Zero" date or time values used through MyODBC are converted automatically to NULL in MyODBC 2.50.12 and above, because ODBC can't handle such values.
MyODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.
The special date '0000-00-00' can be stored and retrieved as '0000-00-00'. When using a '0000-00-00' date through MyODBC, it will automatically be converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of date.
String functions now return VARCHAR rather than CHAR and the column type is now VARCHAR for fields saved as VARCHAR. This should make the MyODBC driver better, but may break some old MySQL clients that don't handle FIELD_TYPE_VARCHAR the same way as FIELD_TYPE_CHAR.
Understanding MyODBC Options MyODBC allows the user to specify several options affecting the behavior of the ODBC connection. I don't know what all of the options do, but will present those I have used or can find a description of. I will be updating these in the future.
Don't optimize column width.
Return matching rows.
Trace MyODBC. This option activates logging of SQL statements sent to the MySQL server. It is useful when you encounter difficulties connecting to the database. The log is written to the file "myodbc.log" on the C:\ drive. (Note that you must use MYSQL.DLL and not MYSQL2.DLL for this option to work!) Once you have generated a log file, check the queries that MyODBC sends to the MySQL server; You should be able to find the queries by searching for the string ">mysql_real_query" in the "myodbc.log" file.
Allow BIG results. SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.
Don't prompt on connect. When initiating an ODBC connection, do not ask the user to manually enter username and password (and possibly other information).