12
Tue, Nov
6 New Articles

TechTip: Let IBM i Apps Access Microsoft Access with Jackcess

Java
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

 

120211SansoterraFigure1

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).

 

DB2 for i Type

MS Access Type

INT

Long Integer

BIGINT

Long Integer

SMALLINT

Integer

DATE,TIME,TIMESTAMP

Date/Time

CHAR,VARCHAR

Text

NCHAR,NVARCHAR

Text

Binary

Binary

BLOB

OLE Object

CLOB, DBCLOB

Memo

FLOAT

Double

DOUBLE

Double

DECFLOAT

Unsupported

DECIMAL

Currency

NUMERIC

Decimal

 

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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: