ODBC Input Component |
The ODBC (Open Database Connectivity) Input component can be used to retrieve information from a database that supports SQL in its ODBC driver.
|
Inserting an ODBC Input Component |
Before you can use an ODBC Input component in a worksheet, you must first establish a link to the database you want to access. This can be done from either the Data Sources (ODBC) control panel (Windows 2000/XP, located under Administrative Tools) or the ODBC Data Sources control panel (Windows NT).
Open the appropriate control panel for your operating system.
On the User DSN tab, click "Add" to add a database.
In the Create New Data Source dialog, select the driver for the database you want to access. Click "Finish."
Specify the database to which you want to link. Name your data source (which you will later select in the ODBC Input Wizard) and, if desired, enter a description. Click "OK."
Exactly how you carry out this step depends upon the driver you select. For example, if you want to link to a Microsoft Access database, you specify the database by clicking "Specify" and navigating to the appropriate Access (*.mdb) file, then naming the database by filling out the Data Source Name field. Other databases may use a differently named button, but the general procedure of specifying and naming the database is the same, and is required to create a Data Source.
Click "OK" to close the Control Panel.
Once you've linked to the database, you can insert the component as you would any other, launching the ODBC Input Wizard to guide you through the configuration process.
On the first page of the Wizard, select a linked data source. If the database requires authorization, enter a valid username and password.
The second page of the Wizard allows you to specify the table from which the component reads data. A component can only access one table. After you select a table, you can select the fields associated with that table that you want to read. By default, Mathcad only shows you fields containing supported data types.
Once you've selected at least one field, you can click "Finish" to exit the Wizard and insert the component.
|
Configuring an ODBC Input Component |
Right-clicking on the component and selecting Properties allows you to control what data the component sends to Mathcad. Unlike the other components, the ODBC Input component supports only one output and no inputs.
The Data Source tab of the ODBC Input Component Properties dialog specifies the data accessed by the ODBC Input component.
If you want to change the data source for the component, the Properties dialog lists all of the linked databases on your system or network. If the database you want to access is not listed here, you first need to link to it. If you change your data source, click "Connect" to refresh the contents of the Properties dialog.
Some databases require you to enter a username and password before granting you access. If your database requires authentication, enter a valid username and password for restricted databases. If the database is not password protected, these fields must be left blank.
Use the Select Table and Select Fields lists to specify the data read in from the data source. By default, only field types supported by Mathcad are listed; to see all of the available fields, check "Show fields with unsupported data types."
You can use the Advanced tab of the ODBC Input Component Properties dialog to further refine the data supplied by the ODBC Input component.
If you know SQL, you can enter a where clause to prefilter your data before importing it.
You can also control the order in which the ODBC Input component sends data to Mathcad. Fields are read from top to bottom, and appear in Mathcad running from left to right.
The ODBC Input component can add a row of column labels to the data it reads from the data source. When accessing the data in Mathcad, the first row will contain the column labels.
Each ODBC Input component can only access one table per database. In order to access another table from your database, use another component.
Checking the "Show fields with unsupported data types" option in the ODBC Input Wizard or on the Data Source tab of the Properties dialog displays all data fields, even those not supported by Mathcad variables. For example, Mathcad does not support any time data types, but you can select and display time indices from your database in a Mathcad output variable.
Database applications must support SQL in their ODBC drivers, such as Microsoft Access or FoxPro. There are some programs that have SQL support within their applications, but do not support SQL in their ODBC drivers, such as Microsoft Excel. For more information about ODBC and SQL support, check the documentation that comes with your database application.