Database and ODBC

Data Splitter can interact with a user-defined database.   It uses ODBC, "Open Database Connectivity", to :

The user must :

  1. Create the database,
  2. Configure an ODBC "Data Source Name" for the database,
  3. Connect the Data Splitter solution to the ODBC data source.

ODBC allows software products to interact with a wide variety of Database Management Systems (DBMSs).   The DBMS must provide a software component known as an ODBC "driver".   Most DBMSs available on Windows platforms provide ODBC drivers.

A simple database can be created in a matter of minutes.   Steps 2 and 3 are even simpler, once you know how ...

Creating a database

The user must provide the "target" database.   This will typically involve using a DBMS (Database Management System) to create one or more tables.

A table is defined as a group of "columns", or "fields".   Each field has attributes :

Here's how to create a database using Microsoft Access :

  1. Select the "File/New" menu option and choose the Database icon,
  2. Choose a name for your new database,
  3. Select one of the "Create table" options,
  4. Define your fields (this may require some thought, and revision!),
  5. Save your new database / table - this creates a new file with the .MDB extension.

A few hints :

At times Data Splitter will appear to scan the input correctly, but the target database will be empty or missing some expected data.   The cause is very often a mismatched data type, or insufficient field width(s).   A good general strategy is to "relax" the table initially - create all "memo" fields, or "text(255)", then tighten the table up after it's loading correctly.

Check the program's status box at the bottom after a run - if it says "View log for messages" there's something that needs attention.   Check the log (View/Log option).   The messages are sometimes cryptic, but helpful more often than not.   If you're having trouble you can submit the log (if it's huge just send the top 50 / bottom 50 lines or so).

Contact Data Splitter support for assistance with database issues.

Configuring an ODBC Data Source

The user must configure an ODBC "Data Source Name" (DSN) for the database.   This is a simple process of choosing a label and applying it to the database.   A Windows program:  the ODBC Data Source Administrator, is used for this purpose.

The ODBC Data Source Administrator program can be started in several ways :

These options are presented because the ODBC Data Source Administrator can be difficult to locate on some systems.   The program's file name is ODBCAD32.EXE, and it is often (but not always) in the \WINDOWS\SYSTEM32 directory.   Contact Data Splitter support if you have trouble finding it.

Once you are running the ODBC Data Source Administrator choose the "Add" option in the first dialog to add a new Data Source :

screen shot: ODBC Data Source Administrator

In the next dialog choose the appropriate ODBC driver for your DBMS :

screen shot: Create New Data Source dialog

The next dialog, which is DBMS-specific, allows you to specify the Data Source Name and the location of your database :

screen shot: ODBC Microsoft Access Setup

Once you have specified the Data Source Name, the driver, and the database location you are ready for the next step - connecting Data Splitter to the Data Source you have just configured ...

Connecting Data Splitter to the Data Source

After setting up the database and the ODBC data source, configure the new Data Source Name in Data Splitter :

This will establish Data Splitter's connection with the database and make the database table and field names visible within the Data Splitter solution as "targets".

SQL statement for creating sample table "message" :   SQLMessage.txt.

SQL statement that works with Microsoft Access :   SQLMessageA.txt.   Memo fields are used rather than char(n) (because MS Access fails to load the record if the character allocation for any field is exceeded).

Data Splitter has been tested with Microsoft Access 2000-2003 and Microsoft Visual FoxPro 6.0, and should work with any DBMS that provides a suitable ODBC driver.