ODBC Access Maintenance

Document path Reference Contents > Advanced Functions > ODBC Access Maintenance

ODBC (Open DataBase Connectivity) is a software mechanism through which programs can access data from an independent data source through standard SQL (Structured Query Language) syntax. For example, you can use Microsoft Excel to bring data through ODBC from a Caliach Vision data file.

This capability is only available on certain platforms (Windows 95, 98 and NT and Power Macintosh (subject to release by Raining Data Inc.)).

Installing the ODBC driver

The following only applies to Windows platforms. Other platform users should read any ODBC notes supplied with Omnis Studio.

When you install Omnis Studio Design (not a Runtime) a directory in the Omnis directory named 'datafile' will contain a directory named 'odbc'. This contains all relevant ODBC files including notes. To install the Omnis data file ODBC driver:
1. Copy omodbc32.dll into c:\windows\system.
2. Run omodbcin.exe, and select the menu item to install the driver.
The OMNIS driver should now appear in the 32 bit ODBC control panel.

Note NOTE: Because the driver conforms to the version v3.0 ODBC API, it requires the ODBC 3.0 Driver Manager. On Windows, this ships with the data access component of Microsoft Office 97.

The ODBC driver implements functionality specified by version 3 of the Microsoft ODBC API to allow read-only access to an Omnis Studio datafile. This document describes the minimum functionality provided by the driver to allow its conformance to the core ODBC V3 API. For specific details of this API refer to the ODBC Programmer's Reference (ODBC 3.0 Programmer's Reference download from the Microsoft website.

Conformance and Requirements

The ODBC driver supports applications written to conform to both the ODBC V2.0 and V3.0 APIs, but only provides functionality at the V3.0 core level. The Driver Manager maps V2.0 function calls to their V3.0 equivalents for backwards compatibility. The driver requires an ODBC Driver Manager V3.0 such as those provided by Microsoft and Intersolve.

The driver is available for Windows 32 bit and Macintosh PowerPC platforms. All operations are performed synchronously. Under Windows 32 bit platform, the driver can be used in a multi-threaded environment but only allows a single thread to be running at any one time.

ODBC Access to Caliach Vision data

Caliach Vision data can only be read by an ODBC user. No modifications of data can be made. There are two levels of security for the database; user/group file and field access.

The ODBC security system has 32 groups to which users can belong. Each file in the datafile can be set to be accessible or inaccessible to each group. Similarly an individual file field can be set to be accessible to each group.

ODBC Groups can then be mapped to Caliach Vision user Groups. This is carried out in the File -- System Manager -- Privileges window under the Groups pane.

Each user belonging to that Vision Group will inherit that Group's ODBC access group memberships (and will have an appropriate entry in the ODBCUSERS file). To access data with ODBC the user would use the same user Id and password as he or she would use to enter Caliach Vision. For example: Say Joe Bloggs was a member of the Sales group in Caliach Vision's normal Privileges system and the Sales group was mapped to the 1, 5 and 6 ODBC groups. Joe Bloggs would be able to read data through ODBC using his Caliach Vision user Id and password. He would be able to see all files that had been set in one or other of groups 1, 5 and 6.

Example of ODBC Use

To capture date into a Microsoft Excel 97 document:

1. Open a blank spreadsheet document.
2. Go into the "Data" menu and then the "Get External Data" hierarchical menu. Select the "Create New Query" line.
3. The Choose Data Source window will open. In the Databases pane select from the list the "New Data Source" line and click the Ok button.
4. You will be presented with the Create New Data Source dialog window. On it there are 4 questions, fill in as follows:
4.1. Enter a name that you will recognize in future.
4.2. Select the OMNIS driver from the dropdown list.
4.3. Click on the Connect button and select your Caliach Vision data file.
4.4. Optionally you can select a default data file from the database.
5. Click OK and you will now have created a data source. This can in future be chosen whenever you want to create a new query.

When you choose Data -- Get External Data -- Create New Query again, your new data source will be listed. If selected, you can set up the query details with the Query Wizard.

Query Wizard in Excel 97 (Microsoft Office 97 for Windows)

If you include fields from more than one file, with the intention of joining the files, you will be presented with the Microsoft Query window within which you need to construct your join.

Microsoft Query window in Excel 97 (Microsoft Office 97 for Windows)

Note NOTE: Caliach Subscription Support does not extend to ODBC client applications.

Access Maintenance

ODBC Access Maintenance and File Access pane

The data contained in this window is held on the datafile in the same record as the Company Details. The system supports 32 groups. A users has access to any File or Field when he belongs to a group that has been granted access to the File or Field. By default all fields are accessible to all groups unless a specific Field exception is created.

The left hand list enables you to name each of the 32 groups. There is no functional use for these names other than to act as an aide-memoir to the person setting access. Typical grouping strategies may be by business activity, for example, sales, purchasing, ledgers, engineering, etc..

You can edit the group names in the list (click and pause over the name) and when you are happy, click on the Save Group Names button.

Tip TIP: To edit cells in the list, select the line and click in the cell and hover the mouse over the cell. I.E. click once and pause with no mouse movement. The cell will then expand into an editable box in which you can enter different data. On leaving the box with any action like a tab or click elsewhere, the data is verified and the list returns to normal with the new data shown in the cell.

The tab panes can be switched between showing File access, or Field Exception access. Both panes operate in the same way by manipulating the data in the list and when satisfied clicking on the Save button below, to save your changes.

The Group names list, File Access List and Field Exceptions list are all stored in the datafile alongside the Company Details. In themselves they DO NOT apply any settings to the datafile as far as is seen by the ODBC driver. The ODBC driver sees only the internal data dictionary and the ODBCUSERS file. After any set of changes to the lists it is necessary to Apply all saved settings to the datafile. This process takes the contents of the lists and internally applies them first to the Caliach Vision program data dictionary, and second updates the datafile data dictionary in accordance with the program data dictionary.

This all happens in one operation but can sometimes take some time and requires all other users to be off the datafile.

Once the datafile has the settings applied, ODBC access will operate correctly.

Note NOTE: The ODBC driver only allows users to read data and never to change or delete data.

Button

Action

Save Group Names

Saves the group names to the datafile.

Apply All Saved Settings to the Data File

Applies the stored settings to the datafile. All other users must be off the system.

Print

Prints a report of the settings. Right-click to set report destination.

The window has two tab panes.

File Access

Field Exceptions Access

File Access

Sets ODBC access at a file level.

Field

Description

File name

The file slot name.

Description

File slot description

Checkboxes

There is a checkbox for each of the 32 groups.

Button

Action

Save File Access

Saves the file access settings to the data file - BUT DOES NOT APPLY THEM TO THE DATA DICTIONARY.

Set All

The round green button will set access for all groups.

Clear All

The round red button will clear access for all groups.

Back to top

Field Exceptions Access

ODBC Access Maintenance and Field Exceptions Access pane

Sets exceptional access to specific fields.

Field

Description

File.Field name

Enter a Filename followed by a fullstop followed by a fieldname.

Description

The description of a valid field name.

Checkboxes

There is a checkbox for each of the 32 groups.

Button

Action

View System Fields

Opens a listing window of all system files and fields. The System Fields window will open.

Save Field Exceptions Access

Saves the field access settings to the data file - BUT DOES NOT APPLY THEM TO THE DATA DICTIONARY.

Set All

The round green button will set access for all groups.

Clear All

The round red button will clear access for all groups.

Back to top

See also: -

Compiled in Program Version 3.10. Help data last modified 6 AUG 2002 13:07. Class wOdbcSettings last modified 21 MAY 2008 13:31:24.

Document path Reference Contents > Advanced Functions > ODBC Access Maintenance