Skip navigation links
MySQL 5.0 Reference Manual :: 25 Conectores :: 25.1 MySQL Connector/ODBC :: 25.1.4 Connector/ODBC Examples :: 25.1.4.3 Connector/ODBC and Third-Party ODBC Tools
25.1.4.4 Using Connector/ODBC with Crystal Reports »
Section Navigation      [Toggle]

25.1.4.3. Connector/ODBC and Third-Party ODBC Tools

[+/-]

25.1.4.3.1. Applications Tested with Connector/ODBC
25.1.4.3.2. Using Connector/ODBC with Microsoft Word or Excel
25.1.4.3.3. Using Connector/ODBC and Microsoft Access

Once you have configured your Connector/ODBC DSN, you can access your MySQL database through any application that supports the ODBC interface, including programming languages and third-party applications. This section contains guides and help on using Connector/ODBC with various ODBC-compatible tools and applications, including Microsoft Word, Microsoft Excel and Adobe/Macromedia ColdFusion.

25.1.4.3.1. Applications Tested with Connector/ODBC

Connector/ODBC has been tested with the following applications:

Publisher Application Notes
Adobe ColdFusion Formerly Macromedia ColdFusion
Borland C++ Builder  
  Builder 4  
  Delphi  
Business Objects Crystal Reports  
Claris Filemaker Pro  
Corel Paradox  
Computer Associates Visual Objects Also known as CAVO
  AllFusion ERwin Data Modeler  
Gupta Team Developer Previously known as Centura Team Developer; Gupta SQL/Windows
Gensym G2-ODBC Bridge  
Inline iHTML  
Lotus Notes Versions 4.5 and 4.6
Microsoft Access  
  Excel  
  Visio Enterprise  
  Visual C++  
  Visual Basic  
  ODBC.NET Using C#, Visual Basic, C++
  FoxPro  
  Visual Interdev  
OpenOffice.org OpenOffice.org  
Perl DBD::ODBC  
Pervasive Software DataJunction  
Sambar Technologies Sambar Server  
SPSS SPSS  
SoftVelocity Clarion  
SQLExpress SQLExpress for Xbase++  
Sun StarOffice  
SunSystems Vision  
Sybase PowerBuilder  
  PowerDesigner  
theKompany.com Data Architect  

If you know of any other applications that work with Connector/ODBC, please send mail to about them.

25.1.4.3.2. Using Connector/ODBC with Microsoft Word or Excel

You can use Microsoft Word and Microsoft Excel to access information from a MySQL database using Connector/ODBC. Within Microsoft Word, this facility is most useful when importing data for mailmerge, or for tables and data to be included in reports. Within Microsoft Excel, you can execute queries on your MySQL server and import the data directly into an Excel Worksheet, presenting the data as a series of rows and columns.

With both applications, data is accessed and imported into the application using Microsoft Query , which enables you to execute a query though an ODBC source. You use Microsoft Query to build the SQL statement to be executed, selecting the tables, fields, selection criteria and sort order. For example, to insert information from a table in the World test database into an Excel spreadsheet, using the DSN samples shown in Sección 25.1.3, “Connector/ODBC Configuration”:

  1. Create a new Worksheet.

  2. From the Data menu, choose Import External Data, and then select New Database Query.

  3. Microsoft Query will start. First, you need to choose the data source, by selecting an existing Data Source Name.

    Microsoft Query, Choose Data Source

  4. Within the Query Wizard, you must choose the columns that you want to import. The list of tables available to the user configured through the DSN is shown on the left, the columns that will be added to your query are shown on the right. The columns you choose are equivalent to those in the first section of a SELECT query. Click Next to continue.

    Microsoft Query, Choose Columns

  5. You can filter rows from the query (the equivalent of a WHERE clause) using the Filter Data dialog. Click Next to continue.

    Microsoft Query, Filter Data

  6. Select an (optional) sort order for the data. This is equivalent to using a ORDER BY clause in your SQL query. You can select up to three fields for sorting the information returned by the query. Click Next to continue.

    Microsoft Query, Sort Order

  7. Select the destination for your query. You can select to return the data Microsoft Excel, where you can choose a worksheet and cell where the data will be inserted; you can continue to view the query and results within Microsoft Query, where you can edit the SQL query and further filter and sort the information returned; or you can create an OLAP Cube from the query, which can then be used directly within Microsoft Excel. Click Finish.

    Microsoft Query, Selecting a destination

The same process can be used to import data into a Word document, where the data will be inserted as a table. This can be used for mail merge purposes (where the field data is read from a Word table), or where you want to include data and reports within a report or other document.

25.1.4.3.3. Using Connector/ODBC and Microsoft Access

[+/-]

25.1.4.3.3.1. Exporting Access Data to MySQL
25.1.4.3.3.2. Importing MySQL Data to Access
25.1.4.3.3.3. Linking MySQL Data to Access Tables

You can use MySQL database with Microsoft Access using Connector/ODBC. The MySQL database can be used as an import source, an export source, or as a linked table for direct use within an Access application, so you can use Access as the front-end interface to a MySQL database.

25.1.4.3.3.1. Exporting Access Data to MySQL

To export a table of data from an Access database to MySQL, follow these instructions:

  1. When you open an Access database or an Access project, a Database window appears. It displays shortcuts for creating new database objects and opening existing objects.

    Access Database

  2. Click the name of the table or query you want to export, and then in the File menu, select Export.

  3. In the Export Object Type Object name To dialog box, in the Save As Type box, select ODBC Databases () as shown here:

    Selecting an ODBC Database

  4. In the Export dialog box, enter a name for the file (or use the suggested name), and then select OK.

  5. The Select Data Source dialog box is displayed; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC or Connector/ODBC 3.51 data source that you want to export to. To define a new data source for Connector/ODBC, please Sección 25.1.3.2, “Configuring a Connector/ODBC DSN on Windows”.

Microsoft Access connects to the MySQL Server through this data source and exports new tables and or data.

25.1.4.3.3.2. Importing MySQL Data to Access

To import or link a table or tables from MySQL to Access, follow these instructions:

  1. Open a database, or switch to the Database window for the open database.

  2. To import tables, on the File menu, point to Get External Data, and then click Import. To link tables, on the File menu, point to Get External Data, and then click Link Tables.

  3. In the Import (or Link) dialog box, in the Files Of Type box, select ODBC Databases (). The Select Data Source dialog box lists the defined data sources The Select Data Source dialog box is displayed; it lists the defined data source names.

  4. If the ODBC data source that you selected requires you to log on, enter your login ID and password (additional information might also be required), and then click OK.

  5. Microsoft Access connects to the MySQL server through ODBC data source and displays the list of tables that you can import or link.

  6. Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that uniquely identifies each record, and then click OK.

25.1.4.3.3.3. Linking MySQL Data to Access Tables

Use the following procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.

To view or refresh links:

  1. Open the database that contains links to tables.

  2. On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.

  3. Select the check box for the tables whose links you want to refresh.

  4. Click OK to refresh the links.

Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of <table name> dialog box in which you can specify its the table's new location. If several selected tables have moved to the new location that you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.

To change the path for a set of linked tables:

  1. Open the database that contains links to tables.

  2. On the Tools menu, point to Add-ins (Database Utilities in Access 2000 or newer), and then click Linked Table Manager.

  3. Select the Always Prompt For A New Location check box.

  4. Select the check box for the tables whose links you want to change, and then click OK.

  5. In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.