Working with ODBC

Last reviewed: 02/22/2024
Article ID: R10017

Summary

Data files for the Tabs3 Software are ODBC capable. ODBC capability allows you to retrieve information from the Tabs3 Software data files using applications such as MSQuery, Crystal Reports, Microsoft Excel, etc.; however, you cannot write information to the Tabs3 Software data files via an ODBC driver. Writing directly to the 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 Software. An ODBC driver license for the software can be purchased from the consultant from which you purchased the software license or from our Sales Department.

Note: Accessing Tabs3 Software data using Microsoft SQL Server Management Studio requires the Platinum SQL ODBC Drivers. See KB Article R11417, "Platinum SQL Overview," for more information on Platinum SQL.

Important Note: This article refers to the standard ODBC driver license available to purchase for use with Platinum and non-Platinum edition systems. Firms running the Tabs3 Platinum SQL Software must use the included ODBC drivers instead. Information on installing and configuring the ODBC driver for Platinum SQL can be found in KB Article R11397, "Working with Platinum SQL ODBC Drivers."

Technical Considerations

64-bit Information

The ODBC driver will work on 64-bit Windows, but the menu shortcut that is created by the installer has a target directory that points to the 64-bit ODBC Administrator. To access the correct ODBC Administrator, click the Windows Start button, type "ODBC," and then click the appropriate administrator from the search results.

Note: Although the ODBC driver will work on 64-bit versions of Windows, it will not work with 64-bit versions of Microsoft Office and other 64-bit software applications. 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.

Note: The Tabs3 Platinum SQL Software includes both 32-bit and 64-bit ODBC drivers which work with 64-bit applications including Microsoft Office. Information on configuring these drivers is available in Knowledge Base Article R11397, "Working with Platinum SQL ODBC Drivers."

Security Considerations

The ODBC driver allows access to all data files in all of the Tabs3 Software. Be aware that anyone who uses the ODBC driver has access to all records in all files in all Tabs3 Software, including secure clients, private calendar records, and clients restricted by assigned timekeepers.

ODBC Driver Requirements

There have been multiple versions of the ODBC driver released, with the newer versions required for the more recent Platinum editions of the Tabs3 Software. Additionally, there is also a separate set of ODBC drivers available for firms using the Platinum SQL Software. The following table provides a breakdown of driver compatibility by Tabs3 Software edition and version.

Edition Version Compatible ODBC Driver Compatible SQL ODBC Driver
Non-Platinum 2022 and later 3.26 N/A
2021 and prior 3.24 (Version 3.25 is not compatible)
Platinum 2022 and later 3.26 N/A
2021 and prior 3.24
Platinum SQL 2022 and later None, use the SQL ODBC Driver instead
(see the Platinum SQL section for more information)
12.0.1
2021 and prior 11.05.01.53867

To determine the currently installed FairCom ODBC driver

  1. On the workstation where the ODBC driver is installed, click the Start menu, search for "ODBC", and open the ODBC Data Sources (32-bit).
  2. Click the Drivers tab.
  3. Locate the entry for the driver. The version will be displayed in the column to the right.
    • The standard ODBC driver will be named "FairCom 32bit ODBC Driver."
    • The Platinum SQL driver will be named "FairCom ODBC Driver" or "c-treeACE ODBC Driver."
  4. Click OK to close the ODBC Data Source Administrator.

Tabs3 Software non-Platinum Edition

Version 2022 and later of the non-Platinum edition of the Tabs3 Software requires Version 3.26 of the standard ODBC driver. Version 2021 and prior of the non-Platinum edition of the Tabs3 Software requires Version 3.24 of the standard ODBC driver (prior versions as well as Version 3.25 are not supported).

Tabs3 Platinum Software

Version 2022 and later of the Platinum edition of the Tabs3 Software requires Version 3.26 of the standard ODBC driver. Version 2021 and prior of the Tabs3 Platinum Software requires Version 3.24 of the standard ODBC driver (prior versions of the driver as well as Version 3.25 are not supported).

Note: Later versions of the ODBC driver will typically not work with earlier versions of Tabs3 Platinum Software, so any updates to the ODBC driver must be performed at the same time as their corresponding Tabs3 Platinum Software updates. See the table in the ODBC Driver Requirements section above for a list of Tabs3 Software versions and their required ODBC drivers.

Note: The error message that occurs when attempting to use an out-of-date ODBC driver with the Tabs3 Platinum Software may vary depending on the application used, but typically contains the phrase "Failed sysdb on beginsession".

Tabs3 Platinum SQL Software

Tabs3 Platinum SQL Software includes separate ODBC drivers. These ODBC drivers have multiple advantages over the standard ODBC driver:

These drivers can be installed from the Current Working Directory on the Platinum server. Each time the Tabs3 Platinum SQL Software is updated to a new version, the ODBC drivers on each workstation may need to be updated as well. See Knowledge Base Article R11397, "Working with Platinum SQL ODBC Drivers," for more information on installing, configuring, and updating the Platinum SQL ODBC drivers.

Note: Tabs3 Platinum SQL Software is not compatible with the standard ODBC driver. Tabs3 Platinum SQL Software must use the Platinum SQL ODBC drivers included with the software instead.

"Error 672" Message

An error 672 has been reported by firms with an ODBC driver older than 3.24. Verify that you have the correct driver installed for the version and product type installed.

File Layouts

Detailed information regarding file layouts can be found in the Tabs3 Billing, Tabs3 Trust Accounting (Trust), Tabs3 Accounts Payable (AP), and Tabs3 General Ledger (GL) 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 by selecting Reports | File Definition Report, or by using the File Maintenance utility. See KB Article R11468, "Platinum SQL and ODBC Table Names," for more information.

Installing the FairCom ODBC Driver

The method of installing the FairCom ODBC driver depends on the currently installed version of the Tabs3 Software.

To install the ODBC driver (Version 2022 and later)

  1. Once your payment has been processed, you will receive an email from Tabs3 Software that your license has been updated.
  2. Download and install the most recent update.
    • For information on installing the most recent update for your current version, see KB Article R11937, "Keeping Tabs3 Software Up To Date."
    • If you already have the most recent update installed, see KB Article R10154, "How to Download Files from Tabs3 Software Download Pages."
  3. From the workstation where ODBC queries are performed, open the Current Working Directory:
    1. Open any Tabs3 Software application from the workstation.
    2. In the Quick Launch, search for and select "About <application name>."
    3. Click the Open CWD button at the bottom of the window.
  4. Open the ODBC_Files folder in the Current Working Directory and double-click ODBCSETUP.EXE to begin the installation process.
  5. Follow the on-screen instructions to run the installer.

To install the ODBC driver (Version 2021 and prior)

  1. Once your payment has been processed, you will receive an email from Hightail indicating that a file has been sent. Click the View Files link in the email.
  2. A Hightail download page will open in your default browser. Click the link to download the file. (Note: If you have a Hightail account associated with your email address, you will be required to sign in.)
  3. Once the download is complete, double-click the downloaded file to launch a self-extracting archive.
  4. Once the files have been extracted, open the folder they were extracted to (C:\Tabs3ODBC by default).
  5. Double-click SETUP.EXE to begin the installation process.
  6. Follow the on-screen instructions to run the SETUP application.
  7. Download and install the most recent software update. See KB Article R10154, "How to Download Files from Tabs3 Software Download Pages," for more information on downloading the most recent update.

Steps to Set Up and Configure an ODBC Data Source

An ODBC Data Source provides ODBC capable software such as MSQuery, Crystal Reports, Microsoft Excel, etc. with an entry point to access data from Tabs3 Software. To configure an ODBC Data Source, you must use the Tabs3 Software to generate a script file. The script file will determine which Tabs3 Software products are available for ODBC access. Once you have configured the script file, you can create the ODBC Data Source using the ODBC Administrator utility.

Configuring a Script File for Use with ODBC

In order to make your firm's data accessible via ODBC, you must generate a script file to provide the ODBC driver with a list of accessible fields in the software. The method used to generate the script file will depend on whether you want to access a single product or multiple products using ODBC.

Single Product

Use the following steps to create a script file for a single Tabs3 Software product (Tabs3 Billing, PracticeMaster, Trust, AP, or GL).

  1. Make sure OT_USR32.DLL is in the Current Working Directory. This special conversion DLL file is installed with the Tabs3 Software.
  2. Launch the Tabs3 Software application you want to access via ODBC.
  3. In the Quick Launch, search for and select "Initialize <application> ODBC." Specify the application's Current Working Directory if requested. You can verify the Current Working Directory via the Quick Launch by searching for and selecting "About <application name>." The Initialize ODBC utility 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 utility 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 utility and specify the Current Working Directory for the data directory.
  4. Proceed to the Creating an ODBC Data Source section.

Version Note: Due to the addition of the Database folder for c-tree data files, the Initialize ODBC utility must be run for each product after updating from a version prior to Version 19.

Multiple Products

Use the following steps to create a combined script file that will allow access to two or more Tabs3 Software products.

  1. Make sure OT_USR32.DLL is in the Current Working Directory. This special conversion DLL file is installed with the Tabs3 Software.
  2. Run Initialize ODBC for each product from which you want to pull information. In the Quick Launch, search for and select "Initialize <application> ODBC." Specify the application's Current Working Directory if requested. You can verify the Current Working Directory via the Quick Launch by searching for and selecting "About <application name>." The Initialize ODBC utility 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 <product> ODBC utility and specify the Current Working Directory for the data directory.
  4.  
  5. Use the following procedure to copy the contents of each of these *.DB files into a single file.
    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 b through d for each *.DB file you created in step 2.
  6. Save the new combined file with the filename "STI.DB".
  7. Proceed to the Creating an ODBC Data Source section.

Version 19 Note: Due to the addition of the Database folder for c-tree data files, the Initialize ODBC utility must be run for each product after updating from a version prior to Version 19.

Creating an ODBC Data Source

Once you have configured the script file, you can proceed with creating the ODBC Data Source.

  1. After installing the FairCom ODBC driver, click the Windows Start button, type "ODBC," and then click the 32-bit ODBC Administrator from the search results. The 32-bit ODBC Administrator has multiple tabs. The only ones we are concerned with are User DSN and System DSN.
  2. Select the User DSN tab (usually selected by default).
  3. Click Add.
  4. From the driver list, select FairCom 32bit ODBC Driver and click Finish to open the configuration window.
  5. The method used to configure the data source will depend on whether your firm is using the Platinum edition of the Tabs3 Software.

Platinum

Use the following steps to configure an ODBC Data Source for a Tabs3 Platinum Software system.

Note: The use of "<" and ">" around a value indicates that it should be replaced by the appropriate value for your system.

    DSN Platinum Configuration
  1. Enter the Data Source Name (i.e., Tabs3 Billing, PracticeMaster, Trust, AP, GL, or STI).
  2. For the Script Name, enter the script name for the application you are configuring.
    • Tabs3.DB for Tabs3 Billing
    • CM.DB for PracticeMaster
    • TAS.DB for Tabs3 Trust Accounting (Trust)
    • APS.DB for Tabs3 Accounts Payable (AP)
    • GLS#.DB for Tabs3 General Ledger (GL) (#  = GL Client Number i.e., GLS1.DB for Client 1. See General Ledger Clients below for more information.)
    • STI.DB for if you created a combined script file for multiple products.

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

  3. For the Driver Type, select TCP/IP.
  4. Change the default Server Name to the server name as specified in the STSRVR.CFG Platinum configuration file (e.g., STI_SERVER@FS4 or STI_SERVER@200.111.222.33). Details regarding the STSRVR.CFG file can be found in the Platinum Server Guide. The server name is case sensitive, so be sure to enter the server name exactly as it appears in the STSRVR.CFG file.
  5. Click Options.
  6. In the Data Dictionary Path, you must enter the full path to the Tabs3 Software's Current Working Directory (e.g., \\<SERVER>\Tabs3). You can verify the Current Working Directory location via Quick Launch by searching for and selecting "About <application name>." This directory must be based on the workstation's point of view; therefore you must enter a local path or UNC path.
  7. In the Special Data Type Conversion DLL Name field, you must enter the full path and file name of the OT_USR32.DLL in the Current Working Directory (e.g., \\<SERVER>\Tabs3\OT_USR32.DLL). This directory must be based on the workstation's point of view; therefore you must enter a local path or UNC path.
  8. Select 1 byte for Alignment.
  9. Click OK.

Non-Platinum

Use the following steps to configure an ODBC Data Source for a non-Platinum Tabs3 Software system.

Note: The use of "<" and ">" around a value indicates that it should be replaced by the appropriate value for your system.

    DSN Standard Configuration
  1. Enter the Data Source Name  (i.e., Tabs3 Billing, PracticeMaster, Trust, AP, GL, or STI).
  2. For the Script Name, enter the script name for the application you are configuring.
    • Tabs3.DB for Tabs3 Billing
    • CM.DB for PracticeMaster
    • TAS.DB for Tabs3 Trust Accounting (Trust)
    • APS.DB for Tabs3 Accounts Payable (AP)
    • GLS#.DB for Tabs3 General Ledger (GL) (#  = GL Client Number i.e., GLS1.DB for Client 1. See General Ledger Clients below for more information.)
    • STI.DB for if you created a combined script file for multiple products.

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

  3. For the Driver Type, select Multi-User (non-Server).
  4. Click Options.
  5. In the Data Dictionary Path, you must enter the full path to the Tabs3 Software's Current Working Directory (e.g., \\<SERVER>\Tabs3). You can verify the Current Working Directory location via Quick Launch by searching for and selecting "About <application name>." (Note: We recommend specifying a UNC path for the Current Working Directory rather than a mapped drive.)
  6. 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 in the Current Working Directory (e.g., \\<SERVER>\Tabs3\OT_USR32.DLL). (Note: We recommend specifying a UNC path for the Current Working Directory rather than a mapped drive.)
  7. For the Sector Size, change this value to 64.
  8. You must select 1 byte for Alignment.
  9. Click OK.

Troubleshooting ODBC

Post-Conversion Steps

Changes made during the conversion process for updates can affect both the ODBC database files and your existing queries. As a result, once you have updated to a new version you may need to perform additional steps to ensure that your ODBC configuration and existing queries are still valid. The following KB Articles include post-conversion steps for firms that use the ODBC driver:

Microsoft Access Index Limit

Microsoft Access has a limit of 32 indexes per table when importing data via ODBC. As of Version 2022, the Tabs3 Billing archive file (t3arch.dat) has more than 32 indexes by default, and therefore cannot be accessed via ODBC by Microsoft Access. Querying a file that exceeds the index limit in Microsoft Access will display the following error:

The operation failed. There are too many indexes on table '<table name>'. Delete some of the indexes on the table and try the operation again.

Column Limit

Firms attempting to access files with more than 256 columns will receive a message stating "Error: Too many columns. Some columns are omitted." This can be resolved using the following steps.

  1. Close the application they are using for ODBC access (e.g., Excel, Access, Crystal Reports).
  2. Open their ODBC data source.
  3. Edit the Table's max number of columns field to a number higher than the number of columns in the file they are trying to access.
  4. Click OK.
  5. Attempt to perform the ODBC query again.

Note: Some applications have a built-in limit on the number of columns they can display (e.g., Microsoft Access has a limit of 255 columns per table). Changing the Table's max number of columns field will not affect this type of limit.

c-tree Error 14 - File Corrupt at Open

This message indicates that the data file you are attempting to access is currently locked by the software while an exclusive task is occurring. Wait for the exclusive task to finish and attempt to perform the query again. See KB Article R11817, "Network and Multi-User Functionality in Tabs3 Software" for more information on exclusive tasks.

c-tree Error 133 - Server is not Active (Platinum Only)

This message indicates that the value in the Server Name field of the ODBC driver configuration does not match the name of the STI Server in the STSRVR.CFG file. See the Platinum section of Creating an ODBC Data Source for more information on this field.

Note: The Server Name field is case-sensitive. You may want to consider copying the server name from the STSRVR.CFG file to ensure an exact match.

ODBC only displays "AutoField1" in the Fields list
     ...or...
Opening t3arch displays "c-tree Error 23: Illegal index member info. (#-4976)"

This indicates a mismatch between the installed version of the ODBC drivers and the OT_USR32.DLL file in the Current Working Directory. To correct this, download and install the latest Version 2022 program files. More information on downloading the most recent update can be found in KB Article R10154, "How to Download Files from Tabs3 Software Download Pages."

General Troubleshooting

Problems with accessing data using the ODBC driver can sometimes be caused by corruption of the CTSYSCAT.FCS file in the Current Working Directory. This can be resolved using the following steps.

  1. Close the application you are using to access the Tabs3 Software data.
  2. Delete CTSYSCAT.FCS from the Tabs3 Current Working Directory.
  3. If using the Platinum edition, restart the STI Server service. See KB Article R11903, "Managing Platinum Services," for more information.
  4. Attempt to access the data using the ODBC driver.

The following is an example of an error that can occur if the CTSYSCAT.FCS file is corrupted:

Excel Troubleshooting

There are two main points to keep in mind when attempting to connect to an ODBC data source using Microsoft Excel:

See the Testing the ODBC Data Source with an Excel Spreadsheet section for steps to connect to an ODBC data source using Excel via Microsoft Query.

Tabs3 General Ledger (GL) Clients

Each GL client's GLS.DB file is located in the Current Working Directory and uses the following naming convention:

       GLS#.DB

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

Note: With the Platinum Edition of GL, the paths stored in each file are relative to the Current Working Directory. The data files are stored in a Database folder within the Current Working Directory.

The following are examples of GLS#.DB files, and they show how the GL 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.

Non-Platinum Platinum
Sample GLS1.DB file
ACCOUNT         C:\Tabs3\Database\g5mstr.dat
JOURNAL         C:\Tabs3\Database\g5trans.dat
RECURRINGENTRY  C:\Tabs3\Database\g5recur.dat
DEPARTMENT      C:\Tabs3\Database\g5dept.dat
JOURNALNAME     C:\Tabs3\Database\g5jrmstr.dat
DEPOSIT         C:\Tabs3\Database\g5jnldep.dat
Sample GLS1.DB file
ACCOUNT         Database\g5mstr.dat
JOURNAL         Database\g5trans.dat
RECURRINGENTRY  Database\g5recur.dat
DEPARTMENT      Database\g5dept.dat
JOURNALNAME     Database\g5jrmstr.dat
DEPOSIT         Database\g5jnldep.dat
Sample GLS2.DB file
ACCOUNT         C:\Tabs3\Database\client2\g5mstr.dat
JOURNAL         C:\Tabs3\Database\client2\g5trans.dat
RECURRINGENTRY  C:\Tabs3\Database\client2\g5jnldep.dat
DEPARTMENT      C:\Tabs3\Database\client2\g5dept.dat
JOURNALNAME     C:\Tabs3\Database\client2\g5jrmstr.dat
DEPOSIT         C:\Tabs3\Database\client2\g5jnldep.dat
Sample GLS2.DB file
ACCOUNT         Database\client2\g5mstr.dat
JOURNAL         Database\client2\g5trans.dat
RECURRINGENTRY  Database\client2\g5recur.dat
DEPARTMENT      Database\client2\g5dept.dat
JOURNALNAME     Database\client2\g5jrmstr.dat
DEPOSIT         Database\client2\g5jnldep.dat

Testing The ODBC Data Source with an Excel Spreadsheet

The following steps will allow you to confirm that the ODBC driver has been configured correctly. This is done by extracting information from the Tabs3 Software database using Microsoft Excel.

  1. Launch Microsoft Excel and select the Data tab from the ribbon.
    Excel Ribbon

  2. Open Microsoft Query.
    1. In Excel 2019 or 2016, click Get External Data, click From Other Sources, then select From Microsoft Query.
      Excel 2016
  3. Microsoft Query will open automatically. Select the data source you created above from the list, select the Use the Query Wizard to create/edit queries check box, and click OK.
    Choose Data Source
     
  4. In the Available tables and columns section, select CLIENT. Click the > button to add all of the fields from the client file to your query as columns and click Next.
    Choose Columns
     
  5. Leave the Filter Data window as is, and click Next.
    Filter Data
     
  6. In the Sort Order window, select CLIENT_ID in the Sort by field and click Next.
    Sort Order
     
  7. In the Finish window, leave the What would you like to do next? option set to Return Data to Microsoft Excel; then click Finish.
    Finish
     
  8. Select the cell on your Excel spreadsheet where you want the table to begin and click OK to import the client file to your Excel spreadsheet.
    Import Data
     
  9. A table listing all of the data from the Tabs3 Client file will be displayed.
    Excel Table

References


©1999-2024 Software Technology, LLC.   All rights reserved. Terms of Use
Tabs3, PracticeMaster, Tabs3Pay, and the "pinwheel" symbol (The "Pinwheel" symbol is a Registered Trademark of Software Technology, LLC.) are registered trademarks of Software Technology, LLC.
Technical support is available via phone or email at (402) 419-2210 or Tabs3.com/support.
Send suggestions for the Knowledge Base to:   kb@Tabs3.com
Knowledge Base:   support.Tabs3.com    Knowledge Base Help
Website:   Tabs3.com
Phone:  (402) 423-1440