Open Database Connectivity (ODBC) is a data access standard you can use to define ACL tables from a variety of data sources. ODBC support in ACL enables you to import data from any ODBC-compliant data source on your computer, or from any location on your network that you have the appropriate security rights to access.

ODBC provides a middle layer, called a database driver, between the data source and ACL. This layer translates ACL queries into commands that the data source understands. ODBC drivers exist for almost all relational databases, and are also available for many non-relational data sources such as spreadsheets and text files.

The following file formats and databases are some examples of data sources that can be defined using ODBC:

• dBASE-compatible files

• Delimited text files

• Microsoft Access

• Microsoft Excel

• IBM DB2

• Microsoft SQL Server

• Oracle

Each of the data sources listed above can also be defined from the file or database using the options specific to that data source in the Data Definition Wizard and the functionality built into ACL or ACL Server. In some cases, you may need to use ODBC instead because you do not have direct access to the data source. For example, you want to define an ACL table from an Access database that is maintained by another department and you can only connect to it using ODBC. In other cases, you can choose which method to use. For example, if your organization has purchased ACL Analytics Exchange you can use either a server profile and database profile, via ACL Analytics Exchange Connector, to connect to a SQL Server database, or you can use an ODBC connection from ACL.

The following guidelines apply when you use ODBC to define an ACL table:

• If you define Microsoft Excel files using ODBC, you must ensure that each column only contains one type of data and that there are no blank rows or columns in the spreadsheet, because these issues may cause data loss during the import. You must also ensure that each field name is unique. Field names are truncated after 31 characters, and if the truncation creates duplicate names, fields with duplicated names will not be displayed in ACL.

• In order to view Excel workbooks in the Select Tables dialog box you must select the System Tables checkbox. Worksheets are identified with a dollar sign ($) appended to the worksheet name, and are categorized as system tables.

• In order to access a particular data source using ODBC, you must have the correct driver for the data source installed. For example, in order to access a Microsoft SQL Server database you must have the appropriate SQL Server ODBC driver installed. In some cases, the required ODBC drivers are installed with the operating system. In other cases, you need to install the ODBC driver provided with the application or one that is available from a third-party vendor. Contact your system administrator if you need assistance locating and installing an ODBC driver.

• You can import both physical database tables and views. To import a table into ACL using ODBC, you need to select the data source to connect to, and then select the particular table and fields to import. You can also filter the records in the table you are importing using a WHERE clause. After you import the data, you can modify the data type and formatting of individual columns.

• If ACL displays a column that is blank except for the title it indicates that the source data field does not contain any data. Custom data types may fail to import successfully through ODBC.

• ACL sets a default of two decimal places for FLOAT data fields because the ODBC specification provides no information on decimals. You can modify the decimal value for a field in the Table Layout dialog box, after you define the ACL table.

• After you import a table through ODBC, you can update the data in the ACL table whenever necessary. The query created when you defined the table is rerun against the data source when you right-click the table in the Overview tab in the Project Navigator and select Refresh from Source.