How do I connect A Reporting Tool (ART) to Microsoft Access?

Use the JDBC-Access Gateway to connect A Reporting Tool (ART) to a Microsoft Access database.

We tested ART with the JDBC-Access Gateway using:

  • ART version 2.5.2.
  • Apache Tomcat version 7.0.42
  • The 32-bit Java Runtime Environment (JRE) included with Java Development Kit (JDK) 1.7.0_40
  • Windows 2008 R2 64-bit

The following instructions assume that:

  • You've already installed a 32-bit JRE.
  • Tomcat is installed in its default location, drive:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0.
  • You've copied your ART WAR file, art.war, to the drive:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\webapps folder.

These instructions show how to connect ART to Microsoft Access:

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

    Install the JDBC-Access Gateway into the default folder.

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

  3. Copy esmdb.jar from drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\32-Bits\Libs to drive:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\lib.
  4. In the Windows Taskbar search box, search for "environment variables." Choose 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. Otherwise, you will get an "Architecture mismatch" mismatch error when you attempt to connect to Microsoft Access.

  5. Stop and start the Apache Tomcat server.
  6. Use a Web browser to connect to ART. For example: 
    http://localhost:8080/art/
  7. Once you have logged into ART, go into the Data Sources section and add a new data source:
    Field Value
    Datasource Name Easysoft
    Database Type Other
    JDBC Driver easysoft.sql.esMdbDriver
    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 (\).

    When you choose OK to save the data source, ART tests the connection. If the connection succeeds you get this output:

    Registering driver easysoft.sql.esMdbDriver ...
...OK

Testing connection ...
...OK
  8. Choose Finish, and then navigate to the Admin Console.
  9. Choose Queries, and then choose Create a new query:
    Field Value
    Name Easysoft Test
    Status Active
    Short Description First Easysoft Test
    Description Testing the JDBC-Access Gateway with ART and Microsoft Access.
    Contact Person myperson@mydomain
    Type Tabular
    Datasource Easysoft
    Uses Rules No
    Show Parameters In Output No
    Display Resultset 0
    Source (SQL) SELECT * FROM Suppliers

    Replace Suppliers with the name of a table in your database.
  10. Choose Save Changes, and then, to test the query, choose Execute.

    If the query succeeds, your Access data will be displayed in a new browser window.