BIRT: BIRT Reporting for Microsoft Access Using JDBC-ODBC Bridge

Occasionally the question comes up asking how can one access a Microsoft Access database within BIRT. There are a number of different ways of doing this. The easiest is to use the Java JDBC-ODBC bridge, set up a ODBC connection on your system, and access it this way. This is the way that we will illustrate in this article. Additionally, you can also use a third part JDBC Access driver, and set it up as you would any other JDBC driver in BIRT. I have yet to find a good, free driver for this, although I haven’t looked either. If you were really looking for punishment, you could write your own driver, but that is a road I have no desire to go down since the simpler solution exists. Keep in mind, the limitation is that the Access database in question needs to be in a path that is accessible for the ODBC hosting system.

For this example, I am using the Northwind Sample Database located at http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN.s

I installed the database to C:\TEMP\

Setting Up ODBC

Now I need to set up an ODBC connection to the Access database. I am using Windows XP Pro, so my instruction will reflect that, using the Classic theme. First, I go to the Start menu, under Control Panel. Under Control Panel, I click on the Administrative Tools icon. Then I click on the Data Sources (ODBC) Icon.

Now, because I want this to be accessible through to the System, I click on the System DSN Tab.

Next, I click on the Add button. In the dialog that pops up, I click on Microsoft Access Driver (*.mdb).

In the following dialog, I fill out the information. I name my database NorthWind, and put in a simple description.

Next, I click on the select button. This will bring up an older Windows 3.1 style file browser. I navigate to the C:\Temp folder, and choose the Northwind Sample Database.

From that point I am done, and hit OK and save this configuration. I test that this setup is valid by creating a dummy .UDL file on my desktop, and using the wizard to do a test connection.

Configuring BIRT

Now that I have an ODBC entry made, I need to configure BIRT to use the JDBC-ODBC bridge and connect it to this database. I create a new BIRT Report Project called JDBC-ODBC Access. Under this project, I create a new report called northwindEmployees.rptdesign.

In the report, I create a new Data Source by opening the Data Explorer tab, and right-mouse clicking on Data Sources and choosing New Data Source.

I name the Data Source NorthWind and specify that I want to use JDBC.

Next, I am presented with the JDBC Driver dialog. I need to configure the ODBC driver, which I have not done yet. Remember, that drivers are persistent in BIRT, so once I create it once, I don’t have to do this again, unless I wipe out my BIRT installation and delete the Eclipse folder. So I click on the Manage Drivers button.

From the Manage Drivers dialog, I click on the Add button.

BIRT needs a JAR file to work with. It is possible to use just the package path, but you would need to custom write something for that. Fortunatly, Sun does have the JAR containing the JDBC bridge in the JRE. Since I have JRE installed to C:\Program Files\Java\jre, I need to go to the lib folder there and find the rt.jar file. The rt.jar file contains the JDBC-ODBC bridge.

Now, I click OK and can see it has been added. Now I want to configure the default JDBC URL and Display name, so I click on the Drivers tab.

As you can see from the above screenshot, I already configured my defaults and display name. I used jdbc:odbc:mydb;UID=me;PWD=secret as my URL Template, and the name of my choosing. Now, I just click OK.

From my list of available drivers, I choose NorthWind, and for the URL, I change it to jdbc:odbc:NorthWind and test my connection.

Now, I create a simple Data Set to test this out. I just select the ID, name, and title of the employees from the Employee table. As you can see below, the table browser is available, so I know my connection is good.

Next, I drag my data set to the report designer, add a little bit of formatting, and I am done.