Defining ODBC data sources

You can use ACL to create tables by importing data from any ODBC-compliant data source you have the necessary network and security rights to access from your workstation. You must also have the required ODBC driver for the data source you are connecting to installed on your workstation. The Data Definition Wizard allows you to select a File or Machine DSN (Data Source Name), and then choose the specific table and fields to import from the data source you are connecting to. You can also add a WHERE clause to limit the records returned by your query.

To create a table from an ODBC data source:

  1. Select File > New > Table.
  2. If the Select Platform for Data Source page is displayed, select Local and click Next.
  3. In the Select Local Data Source page, select ODBC and click Next.

    The Select Data Source dialog box is displayed. You can either select File DSN or Machine DSN.

  4. If you want to select a File DSN, complete the following steps:
    1. In the File Data Source tab, select the DSN file (.dsn) you want to connect to.

      If necessary, you can browse to the local folder or network location where the file is stored. You can select any DSN file as long as it references an ODBC driver on the local computer.

    2. Click OK.
  5. If you want to select a Machine DSN, complete the following steps:
    1. Click the Machine Data Source tab.
    2. Select the Data Source Name to connect to and click OK.
    3. If a password is required, a login dialog is displayed. Enter your user ID and password and click OK.
  6. In the Select Table dialog box, complete the following steps:
    1. Select the database table to import into ACL. You can adjust the following options to filter the tables displayed in the table list:
      • Tables – If this checkbox is selected, database tables are displayed.

      • Views – If this checkbox is selected, database views are displayed.

      • System Tables – If this checkbox is selected, system tables are displayed. System tables are special tables used by the database management system or application you are accessing data from.

      • Synonyms – If this checkbox is selected, synonyms are displayed. Synonyms are aliases that have been created for tables or views.

      • All – Equivalent to selecting the four checkboxes above, whether those checkboxes are selected or not.

      • Owner – Select a database owner from the drop-down list to list only items associated with that database owner.

      • Database – If more than one database can be accessed through the ODBC connection, select the database to access.

    2. (Optional) Deselect the Estimate File Size checkbox if you don't need to display the disk space required and number of records returned by the query in the Select Fields dialog box.
    3. Click Next.
  7. In Save File As, enter a name for the table data file (.fil) you are creating and click Save.

    The Select Fields dialog box is displayed.

  8. If necessary, enter a WHERE clause to limit the rows you are importing from the table. You need to enter the clause without the WHERE keyword. For example, enter Country = 'Canada' to limit your selection to records with “Canada” in the Country field. For information on the syntax you can use in the WHERE text box, see ODBC WHERE clause syntax.

    Click Apply to test the syntax of your WHERE clause and determine how many records will be returned. The number of records returned by your query is displayed in the Disk Space panel at the bottom of the form. Note, however, that this functionality is not available if you deselected the Estimate File Size checkbox in the Select Table dialog box.

  9. By default all fields in the table are selected. You can modify the list of selected fields in the following ways:
    • Select a field in the Selected list and click the left-arrow button to move it to the Available list.

    • Click Remove All to move all fields in the Selected list to the Available list.

    • Select a field in the Available list and click the left-arrow button to move it to the Selected list.

    • Click Add All to move all fields in the Available list to the Selected list.

  10. If necessary, enter a new value in the Maximum Character Length Field. This value controls the maximum length of character fields you are importing into ACL. You can enter any value between 1 and 254. The default value is 50.
  11. If necessary, enter a new value in the Maximum Memo Length Field. This value controls the maximum length of text, note, or memo fields you are importing into ACL. You can enter any value between 1 and 1100. The default value is 100.
  12. Click Next.
  13. Enter a name for the ACL table you are adding to your project and click OK.

    The selected data is imported into an ACL table and displayed in a view.

Section contents



(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback