PHP HelpPHP Help

Online Community that helps beginners learn PHP,
and webmasters solve PHP coding problems

since 1999

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.

Importing a Table From the Remote Access Database

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.

Import ODBC database

The Import Objects dialog will appear. If the table you wish to import is listed, select it. Click OK.

ODBC Import Objects

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.

Importing Access Table

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.

Exporting a Table to the Remote Access Database

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.

Microsoft Access Save As

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.

Select the MySQL driver from the list

See the previous instructions for setting up the MyODBC driver. The MyODBC driver dialog should appear.

MyODBC export

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.

exporting ODBC Databases

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.

Select Data Source

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   5Next:  MyODBC Compatibility and Options »

Copyright © 2018