Defining ACL Server database profile data

If your organization has one or more Windows ACL server products installed and configured, and you have created a server profile and database profile to connect to the data source, you can create ACL tables that directly access data from Oracle, SQL Server, or IBM DB2 databases. Each time you open the ACL table the most current data is retrieved from the database by rerunning the query used to create the ACL table. The WHERE and ORDER clauses can be modified after the ACL table is created.

To create a table using a database profile:

  1. Select File > New > Table.
  2. In the Select Platform for Data Source page, select ACL Server and select the Windows server profile to use from the drop-down list, and click Next.
  3. In the Select ACL Server Data Source page, select Database Profile and select the database profile to use from the drop-down list, and click Next.
  4. In the Select Database/Schema page, select the schema (Oracle) or database (SQL Server and IBM DB2) to access from the Schema drop-down list and click Next.
  5. In the Select Tables page, select the database tables, views, and synonyms/aliases to add to your query by selecting the item in the Available Tables list and clicking the right-arrow button. You can select up to five tables, but if you select more than one table, each additional table you select must be related to a previously selected table. Selecting multiple tables, particularly tables with large numbers of records, will typically result in longer wait times before data is displayed in ACL.

    When you select more than one table, ACL displays the Identify Relationship dialog box which you must use to identify the field in the table you are adding that relates to a table that has already been selected.

  6. Click Next when you have selected the required table(s).
  7. Optional. In the Condition Clause page, select the Edit the CONDITION clause checkbox to enable the text box for editing, enter the condition in the text box using SQL syntax, and click Next. A value is only displayed in the Condition Clause text box if two or more related tables are included in the query. The condition clause specifies how the tables are related.
  8. In the Select Columns page, select the column(s) to add in the Available Columns list and click the right-arrow to add it to the Selected Columns list, or click Add All to add all columns to the Selected Columns list. If you selected multiple tables, you must select at least one column from each table by selecting each of the tables in the Select Source Table drop-down list and selecting the required columns. When you have finished selecting all of the columns you want to include, click Next.
  9. Optional. In the Where and Order Clauses page, enter a WHERE statement if you want to limit the results returned from the database and/or an ORDER statement if you want to specify that the results should be sorted by one or more columns and click Next. For both types of statements, you must enter the statement using SQL syntax without the command keyword. For example, WHERE Value > 1000.00 must be entered as Value > 1000.00
  10. In the Final page, verify the settings for the new ACL table and click Finish.
  11. Enter a name for the ACL table you are adding to your project and click OK.

(C) 2015 ACL Services Ltd. All Rights Reserved.