Easysoft JDBC-Access Gateway Getting Started Guide

How to connect to Microsoft Access or Microsoft Excel from Java.

Contents

Introduction

JDBC is a Java application programming interface (API) that connects Java to relational databases (and other tabular data, such as spreadsheets and flat files). To interact with a database, a Java application uses a JDBC driver. 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 for 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 via the normal ODBC driver.

The Easysoft JDBC-Access Gateway is a JDBC driver for Access that uses the Java Native Interface (JNI) to communicate with the Access ODBC driver library. Because this ODBC driver library also supports Excel, the Easysoft JDBC-Access Gateway also provides JDBC access to Excel.

You can use the Easysoft JDBC-Access Gateway to connect Java applications, servlet or JavaServer Pages (JSP) engines, integrated development environments (IDE), and application servers with Access and Excel.

The Easysoft JDBC-Access Gateway has both a Java component and a native Windows component, which is used to communicate with the Microsoft ODBC driver. By default, Java applets cannot load native code libraries; the Java security mechanism prevents this because it cannot police what happens at native code level. To use the Easysoft JDBC-Access Gateway with a Java applet, your Java security policy needs to allow the applet to load native libraries. For example:

/* Sample user .java.policy file */
grant codeBase "http://my_webserver/my_jdbc_applet_dir/-" {
  permission java.lang.RuntimePermission "loadLibrary.*";
};

Note that using applets with the Easysoft JDBC-Access Gateway is not the only way to publish your Access or Excel data on the Web or manipulate the data from within a Web browser. As mentioned, the Easysoft JDBC-Access Gateway is compatible with servlet or JSP engines (such as Apache Tomcat) and application servers (such as Adobe ColdFusion), and these products allow Web-based applications with database back ends to be created.

Supported Microsoft file formats

Office Access Connectivity Engine and Access Database Engine

The Easysoft JDBC-Access Gateway supports aceodbc.dll, which is Microsoft's current ODBC driver for Access and Excel and is also known as the 2007 Office System Driver and Access Database Engine 2010 Redistributable.

The Office 2007 version of aceodbc.dll supports:

The Office 2010 version of aceodbc.dll supports:

The Office 2013 version of aceodbc.dll supports:

Jet

The Easysoft JDBC-Access Gateway supports odbcjt32.dll, which is Microsoft's Jet-based ODBC driver for Access and Excel (also known as the Microsoft ODBC Desktop Database Drivers).

odbcjt32.dll supports the following Access database file formats:

Although Office Access 2007 introduced a new database file format (.accdb), version 4.0 .mdb files continue to be supported by Access 2007 and later.

In addition, odbcjt32.dll supports .xls format workbooks created in:

Although Office Excel 2007 introduced new spreadsheet file formats (.xlsb, .xlsm, .xlsx), .xls files continue to be supported by Excel 2007 and later.

Before you begin

What you need to know

Obtain this information from your system administrator:

Prerequisite software

Java Runtime Environment Requirements

You need a Java Runtime Environment (JRE) installed on the Windows machine you want to install the Easysoft JDBC-Access Gateway on. The JRE contains the Java virtual machine (JVM), runtime class libraries, and Java application launcher that are necessary to run programs written in the Java programming language. The Easysoft JDBC-Access Gateway is compatible with JRE 1.6.0 and later.

To check whether you have the JRE installed on your machine and that your JRE version is one the Easysoft JDBC-Access Gateway supports, open a Command Prompt window, and enter java -version. For example:

c:\>java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
Java HotSpot(TM) Client VM (build 20.1-b02, mixed mode, sharing)

If the reported JRE version is 1.5.n or earlier (or you get the error "'java' is not recognized as an internal or external command, operable program or batch file.") you need to obtain a JRE for your machine.

Windows versions of the JRE are available to download from:

Note The Java Development Kit (JDK), which includes the JRE, is also available to download from this web page. However, unless you are going to develop a Java application to run against the Easysoft JDBC-Access Gateway, you only need to download the JRE package.

If you have installed a 64-bit version of aceodbc.dll (this is the Microsoft driver that you need if you want to connect to ACCDB database files), you need to install a 64-bit JRE. If the output produced by running java -version contains 64-Bit Server VM, you have a 64-bit JRE. To check whether aceodbc.dll is 64-bit, run ODBC Data Source Administrator, which is located in the Windows Control Panel under Administrative Tools. On Windows 8 and later, double-click the 64-bit ODBC Data Sources icon. On earlier versions of Windows, double-click the ODBC Data Sources icon. In the Drivers tab, if the list contains Microsoft Access Driver (*.mdb, *.accdb), you have the 64-bit version of aceodbc.dll.

If your database is an MDB file, you do not need to install aceodbc.dll. The Easysoft JDBC-Access Gateway will use odbcjt32.dll to connect to your database. odbcjt32.dll is included with the Windows operating system. Because odbcjt32.dll is a 32-bit library, you need to use a 32-bit JRE and a 32-bit version of the Easysoft JDBC-Access Gateway. The 32-bit version of the Easysoft JDBC-Access Gateway is installed in:

easysoft_installation_folder\Libs32

The default location for easysoft_installation_folder is drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway.

Include easysoft_installation_folder\Libs32 in your class path.

Prerequisite software if you're using Office 2003 and earlier file formats

ODBC Desktop Database Drivers

The ODBC Desktop Database Drivers must be installed on the machine on which you install the Easysoft JDBC-Access Gateway. Note that you do not need to install Access or Excel on the Easysoft JDBC-Access Gateway machine.

Since Windows 2000, the ODBC Desktop Database Drivers (one of the Jet Database Engine Components) have shipped with Windows and so should be already present on your machine as part of the Windows installation.

To check whether the ODBC Desktop Database Drivers are installed, use ODBC Data Source Administrator:

  1. Do one of the following:
    • On 32-bit Windows, in the Windows Run dialog box, enter: 
      %windir%\system32\odbcad32.exe
    • On 64-bit Windows, in the Windows Run dialog box, enter: 
      %windir%\syswow64\odbcad32.exe

      Note On 64-bit Windows, the ODBC Data Source Administrator that is accessible from Control Panel is a 64-bit application. The 64-bit ODBC Data Source Administrator only lists 64-bit ODBC drivers, and so cannot be used to check whether the ODBC Desktop Database Drivers are installed.

  2. In ODBC Data Source Administrator, click the Drivers tab.
  3. In the File column, look for the file name ODBCJT32.DLL.

    If ODBCJT32.DLL is listed, the ODBC Desktop Database Drivers are installed, and you have the prerequisite software for the Easysoft JDBC-Access Gateway. Otherwise, you need to ask your system administrator to install the Microsoft driver before you can use the Easysoft JDBC-Access Gateway.

Prerequisite software if you're using Office 2007 and later file formats

If the file you want to connect to is an ACCDB format database (.accdb), you need to install aceodbc.dll on the Easysoft JDBC-Access Gateway machine.

The version of aceodbc.dll that is intended for use with Office 2013 files is available to download from:

http://www.microsoft.com/en-us/download/details.aspx?id=39358

Installing the Easysoft JDBC-Access Gateway

  1. Log into the Easysoft website.

    If you have not yet done so, you need to register first. On the registration form, an asterisk (*) indicates that a field is mandatory.

  2. Download the Easysoft JDBC-Access Gateway distribution.
  3. Save the distribution file to a temporary directory on the machine where you intend to install the Easysoft JDBC-Access Gateway.
  4. Execute the file distribution that you downloaded in the previous step.

    Follow the on screen instructions.

  5. Obtain a free trial licence:
    1. In Easysoft Data Access License Manager, complete the contact information and click Request License.

      The email contact address you supply must be the email address you registered with on the Easysoft web site.

    2. Choose Time Limited Trial and click Next.
    3. Choose Easysoft JDBC-Access Gateway from the drop-down list of products and click Next.
    4. Click On-Line Request. You will get a message telling you that your license has been added. Click OK.
    5. Click Finish.

Using the Easysoft JDBC-Access Gateway

Setting the class path

The Java class library file for the Easysoft JDBC-Access Gateway is named esmdb.jar. As esmdb.jar is not part of the Java platform, the class path must include the esmdb.jar file. Otherwise, your application will throw a ClassNotFoundException exception when trying to use the Easysoft JDBC-Access Gateway. The class path is a parameter that tells the JVM and Java programs where to find to find third-party and user-defined classes.

esmdb.jar is installed in the following location:

easysoft_installation_folder\Libs

The default location for easysoft_installation_folder is drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway.

What class path configuration is required depends on your application:

Applications that are run at the command prompt

The CLASSPATH environment variable is used, which can be set on a system, user or session basis. For example, this command sets the class path for Java applications run in a particular command prompt session:

C:\MyJavaApps>set CLASSPATH="%CLASSPATH%;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar"

Alternatively, you can specify the class path on the Java command line that runs an application by using the java -classpath option.

C:\MyJavaApps> java -cp ".;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar" MyJavaApp

The following steps show how to set the class path when compiling and running the sample Easysoft JDBC-Access Gateway Java application. (Note that the procedure assumes that you have the JDK installed.)

  1. Save the sample Java code to a file named ConnectToAccess.java.
  2. In a Command Prompt, cd to the directory where you saved ConnectToAccess.java.
  3. Set the class path for the Java compiler and application launcher:

    C:\MyJavaApps>set CLASSPATH="%CLASSPATH%;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar"

  4. Compile and run the sample application. For example: 
    C:\MyJavaApps> "C:\Program Files\Java\jdk1.6.0_26\bin\javac" ConnectToAccess.java
    C:\MyJavaApps> java ConnectToAccess
    JDBC Driver Information
Driver name: easysoft.sql.esMdbDriver
Driver Major Version: 1
Driver Minor Version: 0
.
.
.

Applications that run in an IDE

Each IDE has a different method for setting its class path. Setting the CLASSPATH environment variable will not work. You need to add the esmdb.jar folder to the IDE class path.

Further information

Servlets and JSPs

Servlets and JSPs are run in a servlet or JSP engine such as Apache Tomcat. The class path must be set according to the servlet or JSP engine documentation. Setting the CLASSPATH environment variable will not work. You may have to set the engine class path through a GUI or configuration file. Alternatively, you may have to copy esmdb.jar to a particular folder such as lib.

Further information

Connecting to your Access database or Excel workbook

The Easysoft JDBC-Access Gateway provides two JDBC drivers: a driver for Access databases and a driver for Excel workbooks.

The Java class for the Access JDBC driver is easysoft.sql.esMdbDriver. The Java class for the Excel JDBC driver is easysoft.sql.esXlsDriver.

To register a Easysoft JDBC-Access Gateway JDBC driver, your Java application must specify the appropriate class. For example:

// Register the Access JDBC driver.
Class.forName("easysoft.sql.esMdbDriver");

–Or–

// Register the Excel JDBC driver.
Class.forName("easysoft.sql.esXlsDriver");

When the relevant JDBC driver is registered, you can establish a connection by using a connection URL and the getConnection method of the DriverManager class. For example:

String connectionUrl = "jdbc:easysoft:mdb" +
   "?DBQ=C:/Users/Public/Northwind.mdb";
Connection con = DriverManager.getConnection(connectionUrl);

To establish a connection with the Easysoft JDBC-Access Gateway, use a connection URL of the form:

jdbc:easysoft:<filetype>?DBQ=<path>[;<odbc-driver-attribute>=<value>]

where:

Example connection URLs

Opens an Access database for read-only access:

jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb;ReadOnly=True

Opens an Access database that is stored in a shared folder:

jdbc:easysoft:mdb?DBQ=//mymachine/myshare/Sales.accdb;

Opens a password-protected Excel workbook from a mapped network drive:

jdbc:easysoft:xls?DBQ=Z:/Orgdata.xls;PWD=p455w0rd

Appendices

Easysoft JDBC-Access Gateway demonstrators

This sample Java application connects to an Access database and returns a list of tables in the database. The application also prints out information about the database and the Easysoft JDBC-Access Gateway.

import java.sql.*;
import java.util.Properties;

public class ConnectToAccess {

   public static void main(String[] args) {

      // Replace the DBQ value with the path to your Access database.
      String connectionUrl = "jdbc:easysoft:mdb?" +
         "DBQ=C:/Users/Public/Northwind.mdb";

      Driver driver = null;
      DriverPropertyInfo props[] = null;
      Connection con = null;
      DatabaseMetaData dm = null;
      ResultSet rs = null;

      try {
         // Register the Easysoft JDBC-Access Gateway.
         Class.forName("easysoft.sql.esMdbDriver");
         driver = DriverManager.getDriver(connectionUrl);

         System.out.println("JDBC Driver Information");
         System.out.println ("\tDriver name: " +
                     driver.getClass().getName());
         System.out.println("\tDriver Major Version: " +
                     driver.getMajorVersion());
         System.out.println("\tDriver Minor Version: " +
                     driver.getMinorVersion());

         con = DriverManager.getConnection(connectionUrl);

         dm = con.getMetaData();

         System.out.println("\tJDBC Major Version: " +
                     dm.getJDBCMajorVersion());
         System.out.println("\tJDBC Minor Version: " +
                     dm.getJDBCMinorVersion());

         props = driver.getPropertyInfo (connectionUrl, new Properties());

         /* These attributes are Microsoft Access ODBC driver attributes,
            which the Easysoft JDBC-Access Gateway passes to the
            Microsoft driver. Only the DBQ attribute is mandatory. */
         System.out.println ("JDBC URL Attributes");

         for (int i = 0; i < props.length; i++) {
             System.out.print ("\t" + props[i].name);
             System.out.print (" = ");
             System.out.print (props[i].value);
             System.out.print (" : ");
             System.out.println (props[i].description +".");
         }

         System.out.println("Database Information");
         System.out.println("\tDatabase Name: " +
                     dm.getDatabaseProductName());
         System.out.println("\tDatabase Version: " +
                     dm.getDatabaseProductVersion());

         rs = dm.getTables(null, null, "%", null);

         System.out.println("Database Tables");

         // Retrieve type and name for each table in the database.
         while (rs.next()) {
             System.out.println("\t" + rs.getString(3) +
                     " : " + rs.getString(4));
         }
      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
          e.printStackTrace();
      }
      finally {
          if (rs != null) try { rs.close(); } catch(Exception e) {}
          if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }
}

This sample JSP connects to and retrieves data from a JDBC data source that has been configured as a Java Naming and Directory Interface (JNDI) resource named Northwind.

<html>
 <head>
   <title>Sample Easysoft JDBC-Access Gateway JSP</title>

 </head>
 <body>
   <%@ page import="javax.naming.*" %>
   <%@ page import="java.sql.*" %>
   <%@ page import="javax.sql.*" %>
   <%@ page import="java.io.PrintWriter" %>

   <%@page contentType="text/html;charset=UTF-8"%>

   <h1>Sample Easysoft JDBC-Access Gateway JSP</h1>

   <%

   Connection con = null;
   Statement stmt = null;
   ResultSet rs = null;

   try {

       // Obtain our environment naming context
       Context initCtx = new InitialContext();
       Context envCtx = (Context) initCtx.lookup("java:comp/env");

       // Look up our data source
       DataSource ds = (DataSource) envCtx.lookup("jdbc/Northwind");

       // Allocate and use a connection from the pool
       con = ds.getConnection();

       // Fetch and display data
       stmt = con.createStatement();

       // You need to edit this query
       rs = stmt.executeQuery("SELECT CompanyName FROM suppliers");

       while (rs.next()) {
           // You need to edit this column name
           String s = rs.getString("CompanyName");
           out.print(s + "<br>");
       }

       rs.close();
       rs = null;
       stmt.close();
       stmt = null;
       con.close(); // Return to connection pool
       con = null;  // Make sure we do not close it twice
   } catch (SQLException e) {
       out.print("Throw e" + e);
   } finally {
     // Always make sure result sets and statements are closed,
     // and the connection is returned to the pool
     if (rs != null) {
       try { rs.close(); } catch (SQLException e) { ; }
       rs = null;
     }
     if (stmt != null) {
       try { stmt.close(); } catch (SQLException e) { ; }
       stmt = null;
     }
     if (con != null) {
       try { con.close(); } catch (SQLException e) { ; }
       con = null;
     }
   }

   %>

 </body>
</html>

Using the Easysoft JDBC-Access Gateway with Eclipse IDE for Java Developers

The following steps show how to develop and run a Java application for the Easysoft JDBC-Access Gateway in Eclipse IDE for Java Developers.

  1. In Eclipse, choose File > New > Java Project.
  2. In the Create a Java Project dialog box, enter Easysoft in the Project Name box. Click Use a project specific JRE, and choose jre1.6.0 or higher from the list. Click Finish.
  3. In the Package Explorer pane, right-click Easysoft and click Properties.
  4. In the Properties for Easysoft dialog box, click Java Build Path.
  5. In the Libraries tab, click Add External JARS....
  6. In the JAR Selection dialog box, browse to the easysoft_installation_folder\Libs folder, and then double-click esmdb.jar.
  7. In the Package Explorer pane, right-click Easysoft and click New > Java Class.
  8. In the New Java Class dialog box, enter ConnectToAccess and click Finish.
  9. Replace the ConnectToAccess stub with the contents of the sample Java code.

    You need to edit the JDBC URL to specify the path to your Access database.

  10. Choose File > Save.
  11. Choose Run > Run.

Using the Easysoft JDBC-Access Gateway with NetBeans IDE

The following steps show how to develop and run a Java application for the Easysoft JDBC-Access Gateway in NetBeans IDE.

  1. In NetBeans IDE, choose File > New > Java Project.
  2. In the New Project dialog box, choose Java in the Categories list. Choose Java Application in the Projects list. Choose Next.
  3. In the Project Name box, enter Easysoft. In the Create Main Class box, enter Easysoft.ConnectToAccess.
  4. In the Projects list, right-click Easysoft and click Properties.
  5. In the Project Properties dialog box, click Libraries in the Categories list.
  6. In the Compile tab, click Add Jar Folder.
  7. In the Add JAR/Folder dialog box, browse to the easysoft_installation_folder\Libs folder, and then double-click esmdb.jar.
  8. Replace the ConnectToAccess stub with the contents of the sample Java code.

    You need to edit the JDBC URL to specify the path to your Access database.

  9. Choose File > Save.
  10. Choose Run > Run Main Project.

Using the Easysoft JDBC-Access Gateway with Embarcadero JBuilder

The following steps show how to develop and run a Java application for the Easysoft JDBC-Access Gateway in Embarcadero JBuilder.

  1. In Embarcadero JBuilder, choose File > New > Java Project.
  2. In the Create a Java Project dialog box, enter Easysoft in the Project Name box.
  3. In the Package Explorer pane, right-click Easysoft and click Properties.
  4. In the Properties for Easysoft dialog box, click Java Build Path.
  5. In the Libraries tab, click Add External JARS....
  6. In the JAR Selection dialog box, browse to the easysoft_installation_folder\Libs folder, and then double-click esmdb.jar.
  7. In the Package Explorer pane, right-click Easysoft and click New > Class.
  8. In the New Java Class dialog box, in the Name box, enter ConnectToAccess and click Finish.
  9. Replace the ConnectToAccess stub with the contents of the sample Java code.

    You need to edit the JDBC URL to specify the path to your Access database.

  10. Choose File > Save.
  11. Choose Run > Run.

    Note If you get the error "Exception in thread "main" java.lang.UnsupportedClassVersionError: Bad version number in .class file", the Easysoft JDBC-Access Gateway is incompatible with the JBuilder JRE. If you have not already done so, install JRE 1.6.0 or later on the Easysoft JDBC-Access Gateway machine. In the JBuilder Package Explorer, right-click Easysoft and click Properties. In the Libraries tab, click JRE System Library [jre], and then click Edit. Click Installed JREs. Click Add. Click Standard VM and then click Next. Click Directory, and then browse to the JRE folder. For example, C:\Program Files\Java\jre6. In the Edit Library dialog box, choose Alternate JRE, and the select your JRE in the list.

Using the Easysoft JDBC-Access Gateway with Apache Tomcat

Apache Tomcat is a Java Servlet or JSP engine that provides a platform for developing and deploying web applications. The Easysoft JDBC-Access Gateway enables a Tomcat web application to provide a frontend to an Access database.

The following steps show how to create an example web application that provides a frontend to the Northwind database.

  1. On a Windows machine running Tomcat, copy esmdb.jar to $CATALINA_HOME\lib.
  2. Create a file named Northwind.xml in $CATALINA_HOME\conf\Catalina\localhost with these contents: 
    <Context path="/Northwind"
        reloadable="true" crossContext="true">

  <Resource name="jdbc/Northwind" auth="Container"
               type="javax.sql.DataSource" maxActive="100"
               maxIdle="30" maxWait="10000"
               driverClassName="easysoft.sql.esMdbDriver"
               url=
"jdbc:easysoft:mdb?DBQ=$CATALINA_HOME\webapps\Northwind\Northwind.mdb"/>

    In the url attribute, replace $CATALINA_HOME with your Tomcat installation folder path. For example, C:\Apache Tomcat\apache-tomcat-7.0.16-windows-x86\apache-tomcat-7.0.16.

  3. Create a folder named Northwind in $CATALINA_HOME\webapps.
  4. Copy Northwind.mdb to $CATALINA_HOME\webapps\Northwind.
  5. Create a folder named WEB-INF in $CATALINA_HOME\webapps\Northwind.
  6. Create a file named web.xml in $CATALINA_HOME\webapps\Northwind\WEB-INF with these contents: 
    <web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>Northwind Web App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/Northwind</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>
  7. Save the contents of the sample JSP to a file named $CATALINA_HOME\webapps\Northwind\Northwind.jsp.
  8. Use a web browser on the Tomcat server machine to log into the Tomcat Web Application Manager:

    http://localhost:8080/manager/html

    If necessary, use the Tomcat Web Application Manager to start your new web application.

  9. To use the web application, go to:

    http://localhost:8080/Northwind/Northwind.jsp

Using the Easysoft JDBC-Access Gateway with Adobe ColdFusion

Adobe ColdFusion is a platform for creating and deploying dynamic web applications. ColdFusion provides ColdFusion Markup Language (CFML), a tag-based scripting language that enables you to enhance standard HTML files. For example, you can use CFML to process form data and query databases.

The Easysoft JDBC-Access Gateway enables a ColdFusion web application to provide a frontend to an Access database.

The following steps show how to create an example ColdFusion page that provides a frontend to the Northwind Suppliers table.

  1. On a Windows machine running ColdFusion, place esmdb.jar on the ColdFusion Class Path by copying the file to cf_root\wwwroot\WEB-INF\lib, where cf_root is the ColdFusion root folder. For example, C:\ColdFusion9.
  2. If ColdFusion is already running, restart the ColdFusion Application Server service. To do this, open a Command Prompt window and run the net stop and net start commands. For example: 
    C:\>net stop "ColdFusion 9 Application Server"
C:\>net start "ColdFusion 9 Application Server"
  3. In a Web browser, log into the ColdFusion Administrator:

    http://localhost:8500/CFIDE/administrator/index.cfm

    If your web browser is running on a different machine to ColdFusion, replace localhost with the name of the ColdFusion machine.

  4. In ColdFusion Administrator, use the Data & Services > Data Sources page to add a new Easysoft JDBC-Access Gateway data source:
    Setting Value
    Data Source Name Enter a descriptive name for the data source. For example, Northwind.
    Driver Other

    Click the Add button.

  5. In the Data & Services > Datasources > Other page, complete the remaining details for the data source:
    Setting Value
    JDBC URL Enter a valid Easysoft JDBC-Access Gateway connection URL. For example, jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb
    Driver Class easysoft.sql.esMdbDriver
    Driver Name Easysoft JDBC-Access Gateway

    Click the Submit button to add your data source.

  6. Create a ColdFusion page named northwind.cfm under the ColdFusion web_root or web_application_root directory and add these lines: 
    <!--- The number of records to display per page. --->
<cfparam name="MaxRows" default="10">
<!--- Start at record 1. --->
<cfparam name="StartRow" default="1">
<!--- Query the Northwind data source --->
<cfquery
    name="GetSuppliers" datasource="Northwind">
    SELECT CompanyName, City, Region
    FROM Suppliers
    ORDER BY CompanyName, Region
</cfquery>
<table cellpadding="1" cellspacing="1" border="1">
    <tr>
        <th>
            &nbsp;
        </th>
        <th>
            Company Name
        </th>
        <th>
            City
        </th>
        <th>
            Region
        </th>
    </tr>
<!--- Display the query results and define the startrow and maxrows
parameters.  Use the query variable CurrentCount to keep track of the
row you are displaying. --->
<cfoutput query="GetSuppliers" startrow="#StartRow#" maxrows="#MaxRows#">
    <tr>
        <td valign="top">
            <b>#GetSuppliers.CurrentRow#</b>
        </td>
        <td valign="top">
            #CompanyName#
        </td>
        <td valign="top">
            #City#
        </td>
        <td valign="top">
            #Region#
        </td>
    </tr>
</cfoutput>
<!--- If there are more records to display, output a link that enables
the user to move through the result set --->
    <tr>
        <td colspan="4">
        <cfif (StartRow + MaxRows) LTE GetSuppliers.RecordCount>
            <cfoutput>
                <a href="#CGI.SCRIPT_NAME#?
startrow=#Evaluate(StartRow + MaxRows)#">
                  See next #MaxRows# rows
                </a>
              </cfoutput>
        </cfif>
        </td>
    </tr>
</table>
  7. Open the ColdFusion page in a Web browser. For example:

    http://localhost:8500/northwind.cfm

Using the Easysoft JDBC-Access Gateway with Oracle WebLogic

Oracle WebLogic is a Java platform for developing, deploying, and integrating enterprise applications.

The following steps show how to retrieve Access data from an Easysoft JDBC-Access Gateway JDBC data source created in WebLogic Server.

  1. On a Windows machine running WebLogic Server, add esmdb.jar to the class path for your WebLogic Server domain. To do this, add the following line to WL_HOME/domain/startWebLogic.cmd: 
    set PRE_CLASSPATH=easysoft_installation_folder\Libs\esmdb.jar

    Replace WL_HOME, domain, and easysoft_installation_folder with appropriate values for your machine. For example, on our test machine, we added the following line to C:\WebLogic\wls1035_dev\mydomain\startWebLogic.cmd:

    set PRE_CLASSPATH=C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar
  2. Either start or restart the Weblogic Server.

    The Weblogic Server startup script, startWebLogic.cmd, is located in the WL_HOME/domain folder.

  3. In a Web browser, log into the WebLogic Server Administration Console:

    http://localhost:7001/console

    If your web browser is running on a different machine to WebLogic Server, replace localhost with the name of the WebLogic Server machine.

  4. In the Domain Structure tree, expand Services > JDBC, then select Data Sources.
  5. On the Summary of Data Sources page, click New > Generic Data Source.
  6. Complete the JDBC Data Source Properties page:
    Property Value
    Name Easysoft JDBC-Access Gateway Data Source
    JNDI Name Northwind
    Database Type Other

    Click Next to move through the Create a New JDBC Data Source, accepting the default values.

  7. Complete the Connection Properties page:
    Property Value
    Database Name Northwind
    Host Name localhost
    Port 0

    Click Next.

  8. Complete the Test Database Connection page:
    Property Value
    Driver Class Name easysoft.sql.esMdbDriver
    URL jdbc:easysoft:mdb?DBQ=path_to_northwind/Northwind.mdb
    Test Table Name SQL SELECT 1

    Click Test Configuration, and then choose Finish.

  9. In the Summary of JDBC Data Sources page, choose your new JDBC data source.
  10. In the Targets tab, select the servers on which you want to deploy the data source. Choose Save.
  11. On a machine where the WebLogic Full Client (wlfullclient.jar) is located, create a JDBC client to test the new data source. For example: 
    import java.sql.*;
import java.util.*;

import javax.naming.*;
import javax.sql.*;

public class WebLogicJDBCClient {

   public static void main(String[] args) {

       Context ctx = null;
       Hashtable ht = new Hashtable();
       ht.put(Context.INITIAL_CONTEXT_FACTORY,
               "weblogic.jndi.WLInitialContextFactory");

       // Substitute myhost with the correct hostname for your
       // WebLogic Server.
       ht.put(Context.PROVIDER_URL,"t3://myhost:7001");
       Connection con = null;
       Statement stmt = null;
       ResultSet rs = null;

       try {
           ctx = new InitialContext(ht);
           DataSource ds = (DataSource) ctx.lookup ("Northwind");
           con = ds.getConnection();

           stmt = con.createStatement();
           stmt.execute("SELECT CompanyName FROM suppliers");
           rs = stmt.getResultSet();

           while (rs.next()) {
                   System.out.println(rs.getString(1));
           }
       }

       catch (Exception e) {
           e.printStackTrace();;
       }

       finally {
          try { ctx.close(); } catch(Exception e) {}
          if (rs != null) try { rs.close(); } catch(Exception e) {}
          if (stmt != null) try { stmt.close(); } catch(Exception e) {}
          if (con != null) try { con.close(); } catch(Exception e) {}
       }
   }
}

    C:\MyJavaApps>set CLASSPATH="%CLASSPATH%;C:\WebLogic\wls1035_dev\wlserver\server\lib\wlfullclient.jar"
    C:\MyJavaApps>"C:\Program Files\Java\jdk1.6.0_26\bin\javac.exe" WebLogicJDBCClient.java
    Exotic Liquids
New Orleans Cajun Delights
Grandma Kelly's Homestead
.
.
.

JDBC tracing

To help resolve issues and problems with the Easysoft JDBC-Access Gateway, you may need to enable JDBC tracing (also known as logging):

Note The Easysoft JDBC-Access Gateway communicates with the Microsoft ODBC driver directly rather than via the Microsoft ODBC Driver Manager. For this reason, the ODBC Driver Manager tracing facility, accessible from the ODBC Data Source Administrator, cannot be used to log Easysoft JDBC-Access Gateway activity. Error messages returned by the ODBC driver are returned by using the JDBC tracing mechanism however.