Use the open-source Jackcess Java library to allow IBM i applications to natively read and write Access database files.

Many IBM i developers are aware of the Apache POI Project, which maintains Java libraries that can read and write Microsoft Office files, including Word, Excel, and PowerPoint (with work being done for Outlook messages, Visio, and Publisher documents). These libraries enable Java developers to create or modify Office documents. One important Office application excluded from this list is Microsoft Access. However, an open-source project called Jackcess fills this gap by providing a Java library that can be used to read and write data within Access databases.

What Does Jackcess Do?
Jackcess allows Java developers to do the following with a Microsoft Access database:

• Read table and query definitions (including system tables)
• Retrieve table relationship information
• Create new tables
• Update and Insert data into tables

A related project called Jackcess Encrypt can be used to read encrypted databases with some limitations (I have not tested this feature).

It cannot do the following type of operations with your databases:

• Execute queries
• Execute module (VBA) or macro code
• Display forms or reports
• Read or modify macro, module, form, or report definitions

In Summary, Jackcess is a utility that can read and write data. It doesn't emulate the numerous other features Access offers.

Getting Started
To get started, download Jackcess 1.2.5 from the SourceForge.net Web site. Be sure to download the dependencies (i.e., Java libraries required for Jackcess to function) and set up your Java project to reference these dependencies. The Javadoc (Java class documentation) for Jackcess is also available online. The Javadoc is a bit sketchy in some areas, and more examples would be helpful. Finally, you'll need to have a minimum of the Java 1.5 JDK (aka Java 5.0) on your system.

Coding Examples
Coding with Jackcess is, as expected with almost any Java API, a pretty simple affair. Here is a code snippet that opens a database and displays its table names, query names, and query definitions:

Database zipcodes=Database.open(

new File("f:\\javalib\\zipcodes.mdb"),false);

for(String table : zipcodes.getTableNames()) {

    System.out.println(table);

}

for(Query query : zipcodes.getQueries()) {

    System.out.println(query.getName());

    System.out.println(query.toSQLString());

}

Likewise, the following snippets show how to reference a table called "Zip Codes," how to iterate through all existing rows, and how to add a new row:

Table table = zipcodes.getTable("ZIP Codes");

for(Map<String, Object> row : table) {

    System.out.println(row.get("ZIP Code"));

    System.out.println(row.get("Latitude"));

    System.out.println(row.get("Longitude"));

    System.out.println(row.get("Class"));

    System.out.println(row.get("City"));

    System.out.println(row.get("State Code"));
}

table.addRow("00003","38","77","C","My Town","02");

Note that when supplying a column name, the row.get method is case-sensitive. The Jackcess "About" page has a few more examples, including how to do an indexed read and how to create a table.

A Warning About Updating Production Data
At this point in Jackcess development, I'd be careful about updating data in production apps. Make sure to test all scenarios thoroughly because there may be some anomalies. For example, I had a table open within Access itself while simultaneously writing to the same database table from a Java application. The newly inserted data from the Java app did not show up within Access until I closed and re-opened the database.

Also, Jackcess does not (at least in my test) pay attention to column constraints (aka validation rules in Access terminology), so I was allowed to insert invalid data into a table. Likewise, I was able to violate a foreign-key constraint when inserting data. (To its credit, it did throw an error when a unique constraint was violated.) Lastly, when opening the Northwind Traders sample database with Jackcess, there were several exceptions thrown and warnings given that concerned me.

Given the complexity of what Access can do, it isn't surprising that problems like this may occur. When updating a production database, it just pays to test carefully, especially if other users will have the database open simultaneously with Jackcess.

A Useful Function for Jackcess in an IBM i Environment
For good old IBM i developers, besides simply reading Access data, Jackcess provides an export function that is very useful. Many IBM i shops have legacy ERP systems and developers are often tasked with querying the data and giving it to users for analysis in Excel or some other tool. Microsoft Access, with its query, filter, form, report, and pivot table capabilities, is an extremely well-suited tool for data analysis.

Jackcess makes it a snap to slam the results of a DB2 for i query into an Access db. Check out this amazingly small and simple class called AccessExport, which can dump the results of almost any DB2 query into an Access table:

import java.io.File;

import com.healthmarketscience.jackcess.*;

import java.sql.*;

public class AccessExport {

    public static void main(String[] args) throws Exception  {

       exportQueryResultToAccDB("SELECT * FROM SalesOrderHeader",

                                "/tmp/export.accdb","Exported_Orders");

        System.out.println("Finished Microsoft Access Export");

    }

    public static void exportQueryResultToAccDB(

        String db2Query,

       String accessFileName,

       String accessExportTableName

       ) throws Exception {

       // Supported file types currently include Access 97 through Access 2010

       Database.create(Database.FileFormat.V2010,new File(accessFileName));

       Class.forName("com.ibm.db2.jdbc.app.DB2Driver");

       Connection connection=DriverManager.getConnection("jdbc:db2://localhost");

       Statement statement=connection.createStatement();

       ResultSet resultSet=statement.executeQuery(db2Query);

       // This statement will copy the result set contents to a table

       // within the Access database

       Database.open(new File(accessFileName)).copyTable(accessExportTableName, resultSet);

       connection.close();

    }

}

There are only seven lines of code in the exportQueryResultToAccDB method! In this example, the supplied DB2 SQL query is executed, and its result is placed in a table called Exported_Orders within a newly created Access database called /tmp/export.accdb. The copyTable method does all of the difficult work to create the new table from the DB2 result set. Because Jackcess is creating the table from scratch, the prior-mentioned concerns about updating data are not applicable. This example assumes that the Java code is running on the IBM i and so uses a local JDBC connection.

For i developers, the exportQueryResultToAccDB method can be revised so that it can be made available to RPG programs using the Java Native Interface (JNI), to all SQL interfaces by registering a Java stored procedure, or to the command line via the RUNJVA command. There are quite a few ways to incorporate this code into your mainstream applications.

After you create an Access mdb or accdb file, you can place it on a Windows share (via QNTC) for Windows users to access, or it can be zipped and emailed, left on the IFS, etc.

Imagine the power of creating a template database containing some pre-built macros, forms, and reports for analyzing data. Your bean counters will post your picture on the wall! Jackcess can be used to refresh the data tables in the template Access database at the desired interval all from the comfort and control of the IBM i OS. (A similar scenario can be accomplished by creating a UI database that uses linked tables referenced in a "data only" Access database. Jackcess can refresh the tables in the "data only" database.)

Figure 1 shows a canned picture of an Access application (taken from Microsoft's MSDN Web site) that shows the power of what can easily be given to an Access user.

Figure 1: A powerful Access Form featuring a pivot table and pivot chart can be created based on your DB2 for i data.

If you've shied away from Access as an analysis tool due to licensing costs, there is hope. If you're willing to do the work to create some initial startup forms and such, you can now let your users run (but not modify) Access applications free of charge using the Access Runtime. You need only one licensed user to create the Access app. Don't forget; Access offers many advantages over Excel when working with large data sets and multiple data sources (i.e., many tables).

A Potential Export Caveat
One thing to beware of when exporting data with Jackcess is that there is an implicit data type conversion. Since DB2 and Access don't share the exact same data types, Jackcess has to pick the best equivalent data type. The table below shows a list that cross-references DB2 for i data types with Access data types (as chosen by Jackcess).

There are potential issues with this implicit data-type conversion. Even though BIGINT is supported, a "Long" is chosen to store this column within Access. Access' Long Integer is just a 32-bit integer and cannot support all of the values of a DB2 BIGINT. Therefore, a DB2 query should cast a BIGINT column to something like NUMERIC(20,0) so that Access will be able to handle the full range of BIGINT values. Other DB2 data types, such as XML and DATALINK, if unsupported (I didn't try them, but I'm guessing they aren't supported) can generally be cast to a string or large object in the export query. DECFLOAT may possibly be cast to a string or other numeric type as long as the receiving type in Access can handle the value range.

In summary, Jackcess offers Java developers the ability to interact with the data in an Access database. While Jackcess may not be mature enough to tackle all of your Access needs, it is certainly up to the tasks of reading and writing to many databases and of creating "export" databases to distribute to users or computer systems that are capable of reading Access databases. Finally, if your shop works with Access databases, you most likely have some component running on a Windows box that has to interface with your trusty AS/400. With Jackcess, you can now get rid of that Windows middleman and control everything from within your IBM i environment.