Searching Microsoft Access Data with Elasticsearch

Elasticsearch has a JDBC importer that allows it to index any data that can be retrieved through a JDBC connection. For example, you can use the Elasticsearch JDBC plugin with the Easysoft JDBC-Access Driver to index Microsoft Access data:

Installing the Easysoft JDBC-Access Driver

  1. Download the JDBC-Access Driver. (Registration required.)
  2. Install and license the JDBC-Access Driver on the machine where Elasticsearch is installed.

    Install the JDBC-Access Driver into the default folder.

    For installation instructions, see the JDBC-Access Driver documentation.

  3. If you are using a 64-bit version of Windows, in Control Panel > System > Advanced System Settings > Environment Variables, double-click PATH in the System variables list. Make sure that the PATH contains: 
    drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\32-Bits\Libs\

    Make sure that this folder appears before C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\ in the PATH.

Connecting Elasticsearch to Microsoft Access

  1. Download the JDBC importer for Elasticsearch to the machine where Elasticsearch is installed and extract the zip file's contents.
  2. Copy the JDBC-Access Driver JAR file to the elasticsearch-jdbc lib subfolder. For example: 
    copy \Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\32-Bits\Libs\esmdb.jar elasticsearch-jdbc-2.3.4.0\lib
  3. Create a new batch script for the JDBC-Access Driver, by making a copy of one supplied with elasticsearch-jdbc: 
    cd elasticsearch-jdbc-2.3.4.0
copy mysql-simple-example.bat ms-access.bat
  4. Edit the batch script so that it looks like this: 
    @echo off

set DIR=%~dp0
set LIB=%DIR%..\lib\*
set BIN=%DIR%..\bin

REM ???
echo {^
    "type" : "jdbc",^
    "jdbc" : {^
        "url" : "jdbc:easysoft:mdb?DBQ=C:/Users/Public/Downloads/Northwind.mdb",^
        "user" : "",^
        "password" : "",^
        "sql" :  "select * from suppliers",^
        "treat_binary_as_string" : true,^
        "index" : "northwind"^
      }^
}^ | "C:\Progra~2\Java\jre1.8.0_131\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter"

    Replace the Java and MDB path with the correct paths on your system.

  5. Start Elasticsearch, if it is not already running.
  6. Run the elasticsearch-jdbc batch file.
  7. In your web browser, go to:

    http://localhost:9200/northwind/_search