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 Gateway to index Microsoft Access data.

Installing the Easysoft JDBC-Access Gateway

If you have not already done so, please register with us to download a fully functional trial version of JDBC-Access Gateway.

1. Download the JDBC-Access Gateway.
2. Install and license the JDBC-Access Gateway on the machine where SQuirreL SQL is installed.

   Install the JDBC-Access Gateway into the default folder.

   For installation instructions, refer to the JDBC-Access Gateway documentation.

3. 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-Excel Gateway\32-Bits\Libs\

   Make sure that this folder appears before C:\Program Files\Easysoft Limited\Easysoft JDBC-Excel 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 Gateway JAR file to the elasticsearch-jdbc-version/lib directory. 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 Gateway, by making a copy of one supplied with Elasticsearch:

   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