MetaServer > Help > How to define an ODB source

How do I define an ODBC source for Database lookup or Export to a Database?

01 ODBC – Introduction

What is ODBC?

ODBC is short for Open Database Connectivity. The main purpose of ODBC is to standardize the connection and communication with databases. Once you have designed your software application to be ODBC compliant, it’s a matter of installing an ODBC driver for your database to be able to communicate with it.

Windows comes with pre-installed ODBC drivers for Excel, Access, CSV text, etc. Many of our DB lookup demos make use of the pre-installed Excel ODBC driver.

What products use ODBC?

Because it is such a widespread standard, any product that features database lookup or export to database supports ODBC. Kofax Express related products using ODBC are:

Kofax Express itself for single field basic database lookup.
CaptureBites MetaTool for multi-field and drill down database lookup.
CaptureBites Database export connector to export to a database.

Stand-alone CaptureBites products using ODBC are:

1. CaptureBites MetaServer for multi-field and drill down database lookup + Export to Database.

Important

The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver , between an application and the DBMS.

The purpose of this layer is to translate the application’s data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant — that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

Both Kofax Express (for database lookups and to load pick lists) and the CaptureBites Database connector (for matching and updating database records with index data and document path location) are ODBC compliant.

Microsoft Windows and Microsoft Office comes with a range of database drivers pre-installed to connect to Excel tables, Access databases, DBase, etc. If you use any of the Microsoft SQL Database Server products, ODBC drivers are bundled with the installation. If you don’t find an ODBC driver for your database on your system, just do a web search for ODBC driver “Your Database Name” and you will find pages where you can download ODBC drivers, often free of charge.

02 Defining an ODBC Source Step by Step

The example below shows how to setup a connection with an Excel sheet, but the process is similar for other database types.

Step 1: open the Control Panel and select Administrative Tools

Step 2: select ODBC Data Sources in the list of Administrative Tools

Step 3: open the System DSN tab and press the Add button. Make sure you create a System DSN (available to all windows users) and not a User DSN (only available to the current windows user).

Step 4: select the driver you want to set up a data source for. For this example, we have selected the Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb) driver.

Step 5: enter a data source name and description and make sure that you select the correct database version. After this, press the Select Workbook button.

Step 6: browse to your database. Make sure you’ve disabled the “Read Only” option. When you’ve selected your database, press OK.

03 Where to find the 32-bit ODBC drivers on the 64-bit version of Windows 7, 8 and 10?

If you are running the 64-bit version of Windows 7, 8 or 10, you may find an empty ODBC driver selection box when you start defining your ODBC source. Something like this:

Or you can only see a couple of 64-bit ODBC drivers you have installed yourself but not the standard 32-bit Excel, Access, DBase, Text, etc. drivers. We want to expose all the 32-bit drivers, like you see in the screenshot below.

To get the a list of 32-bit drivers on the 64-bit version of Windows 7, 8 or 10, you need to make a small adjustment:

Step 1: right-click the Data Sources (ODBC) Link in the Administrative Tools window and select Properties.

Step 2: Change the ‘Target’ to %windir%\syswow64\odbcad32.exe and the ‘Start in’ to %windir%\syswow64. When you’re done, press OK.

If you now define an ODBC source, you will get access to all standard 32-bit ODBC drivers on your 64-bit version of Windows 7, 8 or 10.

You can also find detailed information about this issue in following Microsoft article:
Unable to create DSN for Microsoft Office System Driver on 64-bit versions of Windows