Using MyODBC to Connect to
a 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.
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.
Caution: 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.
Caution: Microsoft Access2000 has bug affecting the export of tables to the 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.
|Pages: 1 2 3 4 5||Next: MyODBC Compatibility and Options »|