SUMMARY: How to configure Clients & Profits for ODBC access.

Technically-saavy users can create custom lists, summaries, forms, and other reports from their Clients & Profits Pro X and Job Tracker X databases using ODBC-compliant applications like Microsoft Excel, Crystal Reports, and Microsoft Access

These applications can access selected data files and fields from the database. Unlike the custom reports that are built into Clients & Profits Pro (and Job Tracker) X, there is virtually no limit to what kinds of data can be accessed using ODBC or what kinds of reports, lists, or analysis can be created.

Creating reports using ODBC is similar to making them with the built-in C&P report writer. Reports are produced by arranging fields from the various C&P data files (e.g., CLIENTS, JOBS, COSTS, JOB_TASKS, JE, etc.). To produce custom reports, you’ll need to know the database structure and the purpose of each field in the data files. For a description of the data files and their fields, please refer to the Data Dictionary.

The C&P Helpdesk does not provide technical support for Crystal Reports, Microsoft Excel, or other applications that use ODBC.

Configuring ODBC

ODBC (which stands for 'Open Data Base Connectivity') was developed by Microsoft to standardize how applications like spreadsheets access databases. It is a standard part of the Windows operating systems, but is optional on the Mac OS. Support for ODBC on the Macintosh is supplied as part of the Microsoft Office software suite.

Before ODBC access to Clients & Profits Pro X can be configured, the ODBC system software has to be properly installed on either your Windows or Macintosh PC. There are versions of the ODBC driver for Windows and Macintosh, some of which won’t support access to the Clients & Profits Pro X database. The version of ODBC installed on your computer must conform to the version 3.0 ODBC API and requires the ODBC 3.x Driver Manager.

The C&P Helpdesk can not provide technical support for configuring your ODBC software. For help configuring your ODBC driver, contact your Windows system adminstrator or qualified consultant.

Access privileges and ODBC

Access to the Clients & Profits Pro X database via ODBC is granted for the computer itself, and not for individual users. Once the computer has been configured for ODBC access to Clients & Profits Pro X, anyone using it can access clients, jobs, costs, and the accounting data without logging in or entering a password. Since ODBC is not user-based, unlike Clients & Profits, be careful about how ODBC access is granted. If an unauthorized user uses a computer that is configured to access the Clients & Profits Pro X data via ODBC, there is the potential for a security issue with your confidential information.

Access to ODBC can be restricted for certain users using a setting in the User Access & Passwords window. Users who can access custom reports can also access the Clients & Profits database using ODBC. To prevent someone from accessing the database from Microsoft Excel, Access, or Crystal Reports, the "Custom Reports/ODBC" access privilege should be unchecked.

To grant a Clients & Profits users ODBC access to the database

1 From Clients & Profits, choose Setup > Users, Access & Passwords.

2 Click on a user name to select it, then click the Access button.

3 Click on the Menus button to display the menu access privileges.

4 Check the Setup option for "Custom Reports/ODBC" then click Save.

To install the Omnis ODBC driver in Windows

There are two parts to establishing ODBC access to Clients & Profits: a DLL and the Omnis ODBC driver. The DLL is copied to the Windows system folder automatically when Clients & Profits is installed. The Omnis ODBC driver is installed separately if you need to access Clients & Profits database using ODBC.

Clients & Profits installs the ODBC driver, named OMODBC32.DLL, install your Windows system folder. (A copy of of the ODBC DLL is also included in the C&P folder, inside a folder called ODBC.)
This step installs the Omnis ODBC driver into the Windows ODBC control panel. The ODBC control panel coordinates access to ODBC databases from the Excel, Crystal Reports, Access and other applications can can access data with it.

1 Open your C&P folder then open your ODBC folder.

2 Double-click on the Configure ODBC application.

3 Choose File > Install Omnis ODBC Driver.

4 Click OK when prompted.

The Omnis ODBC driver is now part of the ODBC manager. If you open the ODBC control panel in Windows, you’ll see a driver called “OMNIS” in the ODBC Data Source Administrator window (i.e., the ODBC control panel).

To configure ODBC for Clients & Profits in Windows

Before applications like Microsoft Excel, Access, and Crystal Reports can access the Clients & Profits, the Omnis ODBC driver needs to be configured. Using the ODBC control panel, you’ll configure the Omnis ODBC driver to allow this computer to access the Clients & Profits database via ODBC.

1 Choose Start > Settings > Control Panels.

2 Double-click on the ODBC control panel.

The ODBC Data Source Administrator window opens, listing the user data sources (i.e., ODBC drivers).

3 Click the Add button.

The Create New Data Source window opens.

4 Select the OMNIS driver then click Finish.

If the OMNIS ODBC driver does not appear, then your ODBC system software is probably outdated. Your computer needs an ODBC driver that conforms with the version 3.0 ODBC API. Before configuring ODBC for Clients & Profits, be sure your PC gets this updated driver.

The OMNIS Data File ODBC Driver Setup dialog box opens, prompting you to enter the user’s ODBC details needed to access the Clients & Profits database.

5 Enter the data source name as C&P then press Tab.

6 Enter the description as Clients & Profits then press Tab.

7 Enter the user name as the user’s Clients & Profits logon initials (i.e., the user’s introduce-yourself initials) then press Tab.

8 Enter the user’s Clients & Profits password.

9 Click the Select button, find your Clients & Profits database, then double-click on it to connect it to the
ODBC driver.

If your database doesn’t appear, it may have reserved (i.e., invalid or unknown) characters in the file name. Reserved characters like slashes, colons, semi-colons, commas, etc. can’t be included in Windows file names. In this case, your database needs to be renamed (e.g., ACCT99.DF1) before being configured for ODBC.

10 Click OK to close the ODBC Driver Setup window.

11 Click OK to close the ODBC Data Source Administrator window.

This computer can now access the Clients & Profits database using Microsoft Excel, Access, Crystal Reports, etc. via ODBC. These steps need to be repeated for each PC or Macintosh that needs ODBC access to Clients & Profits.

To configure ODBC for Clients & Profits on a Macintosh

Before applications like Microsoft Excel, Access, and Crystal Reports can access the Clients & Profits, the Omnis ODBC driver needs to be configured. Using the ODBC control panel, you’ll configure the Omnis ODBC driver to allow this computer to access the Clients & Profits database via ODBC.
ODBC access is not a built-in feature in the Mac OS. Alternatives such as those from Actual Technologies work well in this instance.

The ODBC driver from older versions Microsoft Office are not compatible with Clients & Profits Pro X.

1 Choose Apple > Control Panels > ODBC Setup PPC.

If you don’t see the ODBC Setup PPC control panel, then the Value Pack “data access” software hasn’t been installed from the Office 98 CD. If the control panel appears as “ODBC Setup” without the “PPC” then it is the older version of ODBC, and won’t work with Clients & Profits.

2 Click the Add button.

3 Select the OMNIS ODBC Driver then click Finish.

4 Enter the data source C&P then press Tab.

5 Enter the description as Clients & Profits then press Tab.

6 Enter the user name as the user’s Clients & Profits logon initials (i.e., MGR) then press Tab.

7 Enter the user’s Clients & Profits password.

The user name is case-sensitive. To prevent problems, enter the user name (i.e., the user’s introduce-yourself initials in Clients & Profits) in ALL CAPS.

8 Click OK.

9 Click OK again to close the ODBC control panel.

This Mac can now access the Clients & Profits database using Microsoft Excel via ODBC. These steps need to be repeated for each Macintosh that needs ODBC access to Clients & Profits.