The Define ODBC dialog box is used in a number of applications, depending on its access point:
· From the File / Open Data Source menu option, it s used to view data from an ODBC Data Source
· From the Import branch, it is used to add or edit (including activate or de-activate) an ODBC Import definition. These Import definitions may be saved when they will be used to periodically import data from a given source. In this case, the User Name and Date of the last edit is shown at the bottom of the dialog box. Often, however, it is better to link to the data source so that new data is automatically refreshed in this database when added to the source.
· From the Define Characteristic's Data Source tab, it is used to add or edit an ODBC Link. As mentioned above, linking is often preferred to importing.
In each case, the particular ODBC Data Source (either a file or database) has been previously specified.
Note: Changes in the Import definition will not be updated in any open spreadsheets.
ODBC Name: (Imports only) Enter a unique alphanumeric ODBC Name into the text box provided, up to 60 characters in length.
Note: If the entered ODBC Name already exists in the database, then an Error message is provided when the OK button has been selected, and the user is returned to the Define Import dialog box.
Active Status: (Imports only) Select this option to make the ODBC status Active. There is no difference in functionality for an Active or Inactive ODBC, but the Show Records Where... option can be used to limit the branch items to Active or Inactive items.
Allow other users to see this record: (default ON) When not selected (OFF), this prevents other users from seeing this Import definition, and viewing the data in the ODBC spreadsheet.
The left pane contains a tree view listing of the tables in the database. Beneath each table are the table's Columns.
· Select Show System Tables from the dialog box's Tables menu to see system tables (i.e. tables created by the database). MS Excel considers spreadsheets to be system tables, so this function should be selected (marked with check) to see those items.
· Select Show Views from the dialog box's Tables menu to see user-defined items, such as database-resident query results.
The right pane contains the following tabs:
Columns: Select a column from the left pane and press the Add button, or drag and drop the column into the Columns tab. The order of the Columns determines the column order in resulting spreadsheets. Delete columns from the Import by selecting the X button on the right of the page.
Joins: Select a column from the left pane and press the Add as left or Add as right button, or drag and drop the column into the left or right column in the Joins tab. See: Joins
Criteria: used to limit the Import to data rows matching specific circumstances. See Define ODBC Criteria Tab
Order By: Select a column from the left pane and press the Add button, or drag and drop the column into the left column of the Order By tab. Delete columns from the Sort criteria by selecting the X button on the right of the tab.
· The order of the Columns determines the hierarchy, or sort order: the data will be sorted first based on the data value of the column in the first row; duplicate values of the first criteria will be sorted based on the value of the second row; when data rows have duplicate values of the first and second rows, the value of the third sort parameter will determine the sort order; and so on.
· The right column in the page provides a drop down selection for Ascending and Descending: ascending sort provides alphabetical sorting for all text fields, and increasing values for all numeric fields (field types determined by the source database; blanks will precede data in ascending sort).
Show SQL: This button is used to view or edit the ODBC definition using SQL language.
Add: Select a column name from the list at the left, and use the Add button to specify the column in the right pane’s selected tab (Columns, Joins, Criteria, Order By).
Add as Right: Select a column name from the list at the left, and use the Add as Right button to specify the column in the right-hand side of the right pane’s selected Joins or Criteria tab.
OK: This button is used to accept these changes, but will result in error messages if the tabs are not sufficiently completed. This is usually due to Join conditions not being fully defined for the Columns specified, or if only partial information was entered for one of the Criteria.