Home → EstiMate Version 2 → Database Management → Using the ODBC connector to extract data from the EstiMate data files

5.1. Using the ODBC connector to extract data from the EstiMate data files

This tutorial illustrates how to use the ODBC connector to extract data from the EstiMate data files.

The ODBC connector can be found on your EstiMate 2.0 CD.  Due to licensing restrictions we are not allowed by the vendor to post this connector here for download, however, if you are using 2.0 please email us at CustomerService@PricingMadeEasy.com and we can send you a link to download it after verifying that you are licensed for EstiMate 2.0.

NOTE: You must be using the EstiMate Multi-User Server in order to use the ODBC connector.  It does not work with direct connections to EstiMate data folders. 

Some background information:

1. The database used by EstiMate 2.0 is called NexusDB.  Their website offers more information about the database, but in a nutshell, it is a very fast and stable client-server database system with a small footprint. 
2. The ODBC connector installs into Windows and allows you to use their database from any ODBC-enabled application, such as Microsoft Access or Microsoft Excel, and extract data from the Nexus database.
3. Many items in the 2.0 database are stored in XML format, and it may not look like the type of relational database you are used to working with.  One example is that primary keys are 32-character text strings rather than integers.

Disclaimer: we do not directly support the use of the ODBC connector **at all** beyond this document and you must use it at your own risk.  We strongly recommend that you back up your EstiMate 2 data files before using the ODBC connector, at least until you are comfortable with its operation and "know what you are doing."™

Installing the ODBC connector:

On your EstiMate 2.0 CD you will see an executable file called "NexusDB_V2_ODBC_buildXX.exe" (the build number will change, this does not matter).   See the initial paragraph in this article if you do not have an EstiMate 2.0 CD for how to get the installer from us.

Double click this installer, and click "next through the wizard to install the software.  Installing the ODBC connector is just like installing any other software.

Configuring the ODBC connector:

• In Windows, click Start > Run.
• Type "odbcad32.exe" (without the quotes) in the Run box and click OK.  This will bring up the Windows ODBC Data Source Administrator screen.   Alternatively, you can click Start > Control Panel > Administrative Tools > Data Sources (ODBC) to get to this screen.

 

• Click the "Add" button to add a new data source.
  
• Locate the entry that says "NexusDB V2 Driver" and click "Finish."

In the Server Settings screen, do the following:

• Enter "NexusDB" as the DSN name (this is just an example, you can name it what you like).
• Leave the Transport setting as TCP.
• Enter your server name in the Server Name box.  It will be: EstiMate Server@xxx.xxx.xxx.xxx (the ip address of the machine on which your EstiMate server is running).  If it is the same machine as the one you are installing the ODBC driver on you can enter "EstiMate Server@127.0.0.1" as the server name.
• Leave everything else as is, and click "Test Connection" to verify your connection to the EstiMate Server (Note: your multi-user server must be running for this to work).
  
• Once you have successfully tested the connection, click "Next."

 

On the next page of the wizard, do the following:

• From the Database Name drop down list, select "EstiMate."
• Click the "List Tables" button.  The grid should fill with the list of EstiMate database tables.
• Click "Finish" to save this connection. 

Now you will be back at the ODBC Data Source Administrator screen, and you should have a new connection called "NexusDB" (or whatever you chose to call it).

Using the NexusDB ODBC Connector: 

Now that you have the connector installed and configured, you can use it to extract data from the EstiMate 2 data files.  You can use Excel, Access, or any ODBC-enabled application to do this.  The example provided here uses the OpenOffice 'Base" database program which is an open source (read: free) alternative to Microsoft Access.

First, run OpenOffice Base and you will see the New Database wizard.

• Choose "Connect to existing database"
• Select "ODBC" as the connection type.
  
• Click "Next."

On the second page of the wizard:

• Enter the name of the ODBC connection you created (or browse for it).
• Click "Next" through both of the next pages and click "Finish" (no other settings need to be changed).
• When prompted to save your new database, give it a unique name such as "EstiMate ODBC Database" and save it.

Now you have direct access to the EstiMate 2 database from within the OpenOffice "Base" database.  The same basic principles will apply in Microsoft Access, Microsoft Excel, or any other application that makes use of ODBC database connections within Windows.

We STRONGLY recommend that you do not make ANY changes to the EstiMate 2 data files via the ODBC connector.  PLEASE NOTE that we do NOT support repairing your data files if they are damaged as a result of using the connector, and therefore you really ought to back up your data files before doing anything. ;-)