Connecting SAP Lumira to Microsoft Access

SAP Lumira is written in Java. To interact with a database, a Java application uses a JDBC driver. JDBC is a Java application programming interface (API) that connects Java to relational databases (and other tabular data, such as spreadsheets and flat files). A JDBC driver implements the JDBC API for a particular database.

Microsoft do not produce a JDBC driver for Jet (the underlying database engine for the Microsoft Office product suite) or Jet's successors the Office Access Connectivity Engine and the Access Database Engine. However, Microsoft do produce an ODBC driver for the Office suite. (ODBC is another data access technology, the Microsoft implementation of which is included with Windows.) To provide a JDBC interface to Office applications by using this native Microsoft interface, a JDBC driver must be able to convert JDBC calls to ODBC calls. As far as the Java application is concerned, it is using a normal JDBC driver. As far as the Office application is concerned, it is being accessed by using the normal ODBC driver.

The Easysoft JDBC-Access Gateway is a JDBC driver for Microsoft Access that uses the Java Native Interface (JNI) to communicate with the Access ODBC driver library.

You can use the Easysoft JDBC-Access Gateway to connect Java applications such as SAP Lumira to Microsoft Access.

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 SAP Lumira to Microsoft Access

  1. In SAP Lumira, choose File > Preferences.

    The SAP Lumira Preferences dialog box is displayed.

  2. Choose SQL Drivers.
  3. In the Driver Installation pane, in the Select a Database list, choose Generic JDBC datasource - JDBC Drivers.
  4. Choose the Install Drivers button.

    The Windows Open dialog box is displayed.

  5. Use the dialog box to browse for Easysoft JDBC-Access Gateway JAR file, drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\32-Bits\Libs\esmdb.jar.
  6. Choose the Done button.
  7. Restart SAP Lumira.
  8. In SAP Lumira, choose File > New.

    The Add new dataset dialog box is displayed.

  9. In the Select a source list, choose Query with SQL, then choose the Next button.

    The Add new dataset dialog box is displayed.

  10. In the Select a Database list, choose Generic > Generic JDBC datasource - JDBC Drivers. Choose the Next button.

    The Add new dataset dialog box is displayed.

  11. Complete the dialog box fields:
    Field Value
    User Name dummy
    Password dummy
    JDBC URL jdbc:easysoft:mdb?DBQ=database

    where database is the MDB file that you want to connect to. Include the path, for example, C:/Users/Public/Northwind.mdb. Note that the forward slashes (/) in the path are deliberate, you need to use these instead of backslashes (\).
    JDBC Class easysoft.sql.esMdbDriver
  12. Choose the Connect button.
  13. Choose the Access database tables you want, and then choose the Create button.

    The Access data is now available as a SAP Lumira data set.