Working with ODBC

Last reviewed: 07/13/2010
Article ID: R10017

The information in this article applies to:

• Tabs3 Billing Software Version 15, 14, 12
• PracticeMaster Version 15, 14, 12
• Tabs3 Trust Accounting Software (TAS) Version 15, 14, 12
• Tabs3 General Ledger Software (GLS) Version 15, 14, 12
• Tabs3 Accounts Payable Software (APS) Version 15, 14, 12

SUMMARY

Data files for the software listed above are ODBC capable. ODBC capability allows you to retrieve information from the program data files; however, you cannot write information to the program data files via an ODBC driver. Writing directly to the program data files will corrupt the data. The ODBC driver is written by FairCom Corporation, the manufacturer of the C-tree file storage method used by the Tabs3 and PracticeMaster software. An ODBC driver for the software can be purchased from the reseller from which you purchased the software or our Sales Department.

MSQuery and Crystal Reports are two of the many report writers on the market that will work with ODBC drivers. Other than the information provided in this article, the Tabs3/PracticeMaster Support Staff does not support MSQuery or Crystal Reports.

Note: The ODBC driver will work on 64-bit Windows, but the menu shortcut that is created by the installer has an invalid target directory. In order to fix this shortcut's target so that it points to the correct directory, perform the following:

1. Click on Start | All Programs | Faircom/c-tree Plus ODBC Driver.
2. Right-click on the 32 bit ODBC Administrator and choose Properties.
3. Replace the Target path with C:\Windows\SysWOW64\odbcad32.exe.
4. Click OK to close the Properties window. The shortcut will now point to the correct directory.

Additionally, while the ODBC driver will work on 64-bit versions of Windows, it will not work with 64-bit versions of Microsoft Office. More information about using the ODBC driver with 64-bit versions of Windows can be found in KB Article R11302 - Compatibility with 64-bit Operating Systems and Software.

FILE LAYOUTS

Detailed information regarding file layouts can be found in the Tabs3, TAS, GLS and APS Help under the keyword of ODBC | data file list and in the PracticeMaster Help under the keyword of All Other Files. Detailed File Definitions can be printed for the PracticeMaster files using the File Maintenance program.

Steps to Set Up and Configure an ODBC Data Source to Extract Information from Tabs3/PracticeMaster Software with MSQuery or Crystal Reports:

Configuring ODBC for a Single Tabs3 or PracticeMaster Software Product
Configuring ODBC for a Single Tabs3 or PracticeMaster Software Product (CSV)
Configuring ODBC for Multiple Tabs3 and PracticeMaster Software Products
Configuring ODBC for Multiple Tabs3 and PracticeMaster Software Products (CSV)

Configuring ODBC for a Single Tabs3 or PracticeMaster Software Product

The following steps will help you to troubleshoot problems when configuring or help you set up an ODBC Data Source to extract information from Tabs3, PracticeMaster, GLS, TAS, or APS. The Initialize ODBC program in Tabs3 and PracticeMaster software must be run before the software's data files can be accessed by an ODBC driver to ensure data accuracy.  Furthermore, the ODBC driver must be installed and a User Data Source for the Tabs3 and PracticeMaster software must be configured.

1. Make sure OT_USR32.DLL is in the program directory. This special conversion DLL file is installed with the Tabs3 and PracticeMaster software. 
2. Make sure that the Initialize ODBC program (Utilities | Initialize <program> ODBC) has been run and the program's data directory (i.e., the program's current working directory) was specified. You can verify the current working directory via Help | About.  The Initialize ODBC program will create a *.DB file in the specified directory. 
   
   Note:  The *.DB file includes the location of each data file. If the wrong data directory was specified when the Initialize ODBC program was run, you will have problems. You can open the *.DB file with a text editor to verify that the path shown for each file matches the current working directory. If these paths do not match, rerun the Initialize ODBC program and specify the current working directory for the data directory.
    
3. Make sure that the Data Source is set up properly. To do this, after installing the FairCom ODBC driver, start the ODBC Administrator (Start | Programs | FairCom ODBC Driver | 32bit ODBC Administrator). The 32 bit ODBC Administrator has several different pages. The only ones we are concerned with are "User DSN" and "File DSN".
4. Select the User DSN page (usually selected by default).
5. Click Add.
6. From the driver list, select FairCom 32bit ODBC Driver and click Finish.
7. Enter the Data Source Name (i.e., Tabs3, PracticeMaster, APS, GLS, or TAS).
8. For the Script Name, enter the script name for the program you are configuring. The Script Name must match the name of the file created in Step 2.:
   • Tabs3.DB for Tabs3
   • CM.DB for PracticeMaster
   • APS.DB for APS
   • GLS#.DB for GLS (#  = GLS Client Number i.e., GLS1.DB for Client 1. See GLS Clients below for more information.)
   • GLS.DB for GLS (Version 15.1 and below)
   • TAS.DB for TAS

   Note: Earlier versions of the ODBC driver refer to the Script Name field as the Data Dictionary Name field.

9. For the Driver Type, select Multi-User (non-Server). 

10. Click Options.
11. In the Data Dictionary Path, you must enter the full path of the Tabs3 or PracticeMaster software's current working directory (e.g., C:\Tabs3). You can verify the current working directory location via Help | About.
    
    
12. In the Special Data Type Conversion DLL Name field, you must enter the full path and file name of the special DLL file OT_USR32.DLL. For example, if your program directory is C:\Tabs3, you would enter C:\Tabs3\OT_USR32.DLL.
    
    
13. For the Sector Size, change this value to 64. (Note that when clicking OK, a message may be displayed indicating that a sector size over 16 may require changes to CTSYSCAT.FCS. Simply click Yes and then delete the CTSYSCAT.FCS file from the Tabs3/PracticeMaster program folder on the server.)
14. You must select 1 byte for Alignment.
    
15. Click OK.

Configuring ODBC for a Single Tabs3 or PracticeMaster Software Product (CSV)

The following steps will help you to troubleshoot problems when configuring or help you set up an ODBC Data Source to extract information from Tabs3, PracticeMaster, GLS, TAS, or APS. The Initialize ODBC program in Tabs3 and PracticeMaster software must be run before the software's data files can be accessed by an ODBC driver to ensure data accuracy.  Furthermore, the ODBC driver must be installed and a User Data Source for the Tabs3 and PracticeMaster software must be configured.

1. Make sure OT_USR32.DLL is in the program directory. This special conversion DLL file is installed with the Tabs3 and PracticeMaster software. 
2. Make sure that the Initialize ODBC program (Utilities | Initialize <program> ODBC) has been run. The Initialize ODBC program will create a *.DB file in the specified directory. 
   
   Note: Starting with Version 15.2, the Location of <program> Data field is not available in the Initialize <program> ODBC program in Client Server Versions (CSV) because the field is populated automatically to prevent pathing errors. If using CSV 15.1 and earlier versions of the software, you want to make sure no drive letter or path is specified for the location of each data file. This is because the STI_SERVER is installed in the program directory and all paths should be relative to that directory. Therefore, specify a blank path for the Location of <program> Data field when running the Initialize <program> ODBC program to indicate the location is "the current directory".

   Note:  The *.DB file includes the location of each data file. If the wrong data directory was specified when the Initialize ODBC program was run, you will have problems.

3. Make sure that the Data Source is set up properly. To do this, after installing the FairCom ODBC driver, start the ODBC Administrator (Start | Programs | FairCom ODBC Driver | 32bit ODBC Administrator). The 32 bit ODBC Administrator has several different pages. The only ones we are concerned with are "User DSN" and "File DSN".
4. Select the User DSN page (usually selected by default).
5. Click Add.
6. From the driver list, select FairCom 32bit ODBC Driver and click Finish.
7. Enter the Data Source Name (i.e., Tabs3, PracticeMaster, APS, GLS, or TAS).
8. For the Script Name, enter the script name for the program you are configuring. The Script Name must match the name of the file created in Step 2:
   • TABS3.DB for Tabs3
   • CM.DB for PracticeMaster
   • APS.DB for APS
   • GLS#.DB for GLS (#  = GLS Client Number i.e., GLS1.DB for Client 1. See GLS Clients for information on multiple GLS clients.)
   • GLS.DB for GLS (Version 15.1 and below)
   • TAS.DB for TAS

   Note: Earlier versions of the ODBC driver refer to the Script Name field as the Data Dictionary Name field.

9. For the Driver Type, select TCP/IP. You must also change the default Server Name to the server name as specified in the STSRVR.CFG client server configuration file (for example STI_SERVER@FS4 or STI_SERVER@200.111.222.33). Details regarding the STSRVR.CFG file can be found in the STI Server manual. The server name is case sensitive, so be sure to enter the server name exactly as it appears in the STSRVR.CFG file.

10. Click Options.
11. In the Data Dictionary Path, you must enter the full path of the Tabs3 or PracticeMaster software's current working directory (e.g., C:\Tabs3). You can verify the current working directory location via Help | About. This directory must be based on the workstation's point of view; therefore you will need to enter a local path or UNC path.
    
    
12. In the Special Data Type Conversion DLL Name field, you must enter the full path and file name of the special DLL file OT_USR32.DLL. For example, if your program directory is C:\Tabs3, you would enter C:\Tabs3\OT_USR32.DLL. This directory must be based on the workstation's point of view; therefore you will need to enter a local path or UNC path.
    
    
13. For the Sector Size, change this value to 64. (Note that when clicking OK, a message may be displayed indicating that a sector size over 16 may require changes to CTSYSCAT.FCS. Simply click Yes and then delete the CTSYSCAT.FCS file from the Tabs3/PracticeMaster program folder on the server.)


14. You must select 1 byte for Alignment.
    
15. Click OK.

Configuring ODBC for Multiple Tabs3 and PracticeMaster Software Products

The following steps will help you to troubleshoot problems when configuring or help you set up an ODBC Data Source to extract information from all Tabs3 and PracticeMaster software.

1. Make sure OT_USR32.DLL is in the program directory. This special conversion DLL file is installed with the Tabs3 or PracticeMaster software.
2. Run the Initialize ODBC program for each program from which you want to pull information. This program is found on the Utilities menu. For example, to create the required file for Tabs3, you would run the Initialize Tabs3 ODBC program. Make sure the program's data directory (i.e., the program's current working directory) is specified. You can verify the current working directory via Help | About.  The Initialize ODBC program will create a *.DB file in the specified directory.
3. Using a text editor such as Notepad, open each *.DB file. Verify that the path shown for each data file matches the current working directory. If these paths do not match, rerun the Initialize <program> ODBC program and specify the current working directory for the data directory.
4. Copy the contents of each of these *.DB files into a single file. Use the following procedure to do this:
1. Create a new file using a text editor.
2. In a text editor, open a *.DB file that you created in step 2.
3. Highlight the entire contents of the file and press Ctrl+C to copy the contents to the Windows clipboard.
4. Switch to the new file and then press Ctrl+V to paste the contents of the *.DB file into the new file.
5. Open the next *.DB file. Repeat steps a and d for each *.DB file you created in step 2.
5. Save the new combined file with the filename STI.DB.
6. Make sure the Data Source is set up properly. To do this, after installing the FairCom ODBC driver, start the ODBC Administrator (Start | Programs | FairCom ODBC Driver | 32bit ODBC Administrator). The 32 bit ODBC Administrator has several different pages. The only ones we are concerned with are "User DSN" and "File DSN".
7. Select the User DSN page (usually selected by default).
8. Click Add.
9. From the driver list, select FairCom 32bit ODBC Driver and then click Finish.
10. Enter the Data Source Name (e.g., STI).
11. For the Script Name, enter STI.DB. (Note:  Earlier versions of the ODBC driver refer to the Script Name field as the Data Dictionary Name field.) The Script Name must match the name of the file created in Step 5.
12. For the Sector Size, change this value to 64. (Note that when clicking OK, a message may be displayed indicating that a sector size over 16 may require changes to CTSYSCAT.FCS. Simply click Yes and then delete the CTSYSCAT.FCS file from the Tabs3/PracticeMaster program folder on the server.)
13. For the Driver Type, select Multi-User (non-Server).
14. Click Options.
15. In the Data Dictionary Path, you must enter the full path of the Tabs3 and PracticeMaster software working directory (e.g., C:\Tabs3).
    
16. In the Special Data Type Conversion DLL Name field, you must enter the full path and file name of the special DLL file OT_USR32.DLL. For example, if your program directory is C:\Tabs3, you would enter C:\Tabs3\OT_USR32.DLL.
    
17. You must select 1 byte for Alignment.
    
18. Click OK.

Note:  When you update the Tabs3 and PracticeMaster software to a new version, you will need to create a new STI.DB file.

Configuring ODBC for Multiple Tabs3 and PracticeMaster Software Products (CSV)

The following steps will help you to troubleshoot problems when configuring or help you set up an ODBC Data Source to extract information from all Tabs3 and PracticeMaster software.

1. Make sure OT_USR32.DLL is in the program directory. This special conversion DLL file is installed with the Tabs3 or PracticeMaster software.
2. Run the Initialize ODBC program for each program from which you want to pull information. This program is found on the Utilities menu. For example, to create the required file for Tabs3, you would run the Initialize Tabs3 ODBC program. Make sure the program's data directory (i.e., the program's current working directory) is specified. You can verify the current working directory via Help | About.  The Initialize ODBC program will create a *.DB file in the specified directory.
Make sure no drive letter or path is specified for the location of each data file. This is because the STI_SERVER is installed in the program directory and all paths should be relative to that directory. Therefore, specify a blank path for the Location of <program> Data field when running the Initialize ODBC program to indicate the location is "the current directory".

Version 15.2 Note: The Location of <program> Data field is not available in the Initialize <program> ODBC program because the field is populated automatically to prevent pathing errors.

3. Using a text editor such as Notepad, open each *.DB file. Verify that the path shown for each data file matches the current working directory. If these paths do not match, rerun the Initialize <program> ODBC program and specify the current working directory for the data directory.
4. Copy the contents of each of these *.DB files into a single file. Use the following procedure to do this:
1. Create a new file using a text editor.
2. In a text editor, open a *.DB file that you created in step 2.
3. Highlight the entire contents of the file and press Ctrl+C to copy the contents to the Windows clipboard.
4. Switch to the new file and then press Ctrl+V to paste the contents of the *.DB file into the new file.
5. Open the next *.DB file. Repeat steps a and d for each *.DB file you created in step 2.
5. Save the new combined file with the filename STI.DB.
6. Make sure the Data Source is set up properly. To do this, after installing the FairCom ODBC driver, start the ODBC Administrator (Start | Programs | FairCom ODBC Driver | 32bit ODBC Administrator). The 32 bit ODBC Administrator has several different pages. The only ones we are concerned with are "User DSN" and "File DSN".
7. Select the User DSN page (usually selected by default).
8. Click Add.
9. From the driver list, select FairCom 32bit ODBC Driver and then click Finish.
10. Enter the Data Source Name (e.g., STI).
11. For the Script Name, enter STI.DB. (Note: Earlier versions of the ODBC driver refer to the Script Name field as the Data Dictionary Name field.) The Script Name must match the name of the file created in Step 5.
12. For the Sector Size, change this value to 64. (Note that when clicking OK, a message may be displayed indicating that a sector size over 16 may require changes to CTSYSCAT.FCS. Simply click Yes and then delete the CTSYSCAT.FCS file from the Tabs3/PracticeMaster program folder on the server.)
13. For the Driver Type, select TCP/IP. You must also change the default Server Name to the server name as specified in the STSRVR.CFG client server configuration file (for example STI_SERVER@FS4 or STI_SERVER@200.111.222.33). Details regarding the STSRVR.CFG file can be found in the STI Server manual.  The server name is case sensitive, so be sure to enter the server name exactly as it appears in the STSRVR.CFG file.
     
14. Click Options.
15. In the Data Dictionary Path, you must enter the full path of the Tabs3 and PracticeMaster software working directory (e.g., C:\Tabs3). This directory must be based on the workstation's point of view; therefore you will need to enter a local path or UNC path.
    
    
16. In the Special Data Type Conversion DLL Name field, you must enter the full path and file name of the special DLL file OT_USR32.DLL. For example, if your program directory is C:\Tabs3, you would enter  C:\Tabs3\OT_USR32.DLL. This directory must be based on the workstation's point of view; therefore you will need to enter a local path or UNC path.
    
    
17. You must select 1 byte for Alignment.
    
18. Click OK.

Note:  When you update the Tabs3 and PracticeMaster software to a new version, you will need to create a new STI.DB file.

GLS Clients

In Version 15.1 and earlier, each GLS Client had its own GLS.DB file that was located in the client's data directory. In GLS 15.2 and later, each GLS client's GLS.DB file will be located in the Current Working Directory and will use the following naming convention:

       GLS#.DB

The "#" represents the GLS Client Number.  For example, if a GLS installation has three GLS Clients, there will be GLS1.DB, GLS2.DB, and GLS3.DB files found in the Current Working Directory after the Initialize GLS ODBC program is run for each GLS Client.

Note: With the Client Server Version of GLS, the paths stored in each file are relative to the Current Working Directory.

The following are examples of GLS#.DB files, and they show how the GLS client path is prepended to the appropriate *.DAT file. In this example, there are two GLS#.DB files in the Current Working Directory(C:\Tabs3): GLS1.DB and GLS2.DB. The data for GLS Client 1 is found in the Current Working Directory, while the data for GLS Client 2 is located in the client2 folder found in the Current Working Directory.

ACCOUNT         C:\Tabs3\g5mstr.dat
JOURNAL         C:\Tabs3\g5trans.dat
RECURRINGENTRY  C:\Tabs3\g5recur.dat
DEPARTMENT      C:\Tabs3\g5dept.dat
JOURNALNAME     C:\Tabs3\g5jrmstr.dat
DEPOSIT         C:\Tabs3\g5jnldep.dat

ACCOUNT         g5mstr.dat
JOURNAL         g5trans.dat
RECURRINGENTRY  g5recur.dat
DEPARTMENT      g5dept.dat
JOURNALNAME     g5jrmstr.dat
DEPOSIT         g5jnldep.dat

ACCOUNT         C:\Tabs3\client2\g5mstr.dat
JOURNAL         C:\Tabs3\client2\g5trans.dat
RECURRINGENTRY  C:\Tabs3\client2\g5recur.dat
DEPARTMENT      C:\Tabs3\client2\g5dept.dat
JOURNALNAME     C:\Tabs3\client2\g5jrmstr.dat
DEPOSIT         C:\Tabs3\client2\g5jnldep.dat

ACCOUNT         client2\g5mstr.dat
JOURNAL         client2\g5trans.dat
RECURRINGENTRY  client2\g5recur.dat
DEPARTMENT      client2\g5dept.dat
JOURNALNAME     client2\g5jrmstr.dat
DEPOSIT         client2\g5jnldep.dat

REFERENCES

• Tabs3 Help - Keyword of "ODBC"
• PracticeMaster Help - Keyword of "ODBC"
• TAS Help - Keyword of "ODBC"
• GLS Help - Keyword of "ODBC"
• APS Help - Keyword of "ODBC"

THE INFORMATION PROVIDED IN THE SOFTWARE TECHNOLOGY, INC. KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. SOFTWARE TECHNOLOGY, INC. DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL SOFTWARE TECHNOLOGY, INC. OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF SOFTWARE TECHNOLOGY, INC. OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

© 1999-2010 Software Technology, Inc.   All rights reserved. Terms of Use
The maker of Tabs3 and PracticeMaster
Tabs3, PracticeMaster, and the “pinwheel” symbol () are registered trademarks of Software Technology, Inc.
e-Mail Suggestions for the Knowledge Base to: kb@Tabs3.com
Technical Support via e-mail is not available.
Knowledge Base:   http://support.Tabs3.com
Web Site:   http://www.Tabs3.com