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.
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.
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:
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