Wednesday, February 20, 2008

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

I'm speaking at EclipseCon 2008

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.

19 comments:

Anonymous said...

thanks mate! i needed this one.

hanshof said...

thanks ! that's what i'm looking for

Anonymous said...

Works great. Thanks for posting this!

Anonymous said...

Yeah, this was definitly spot on... nothing more to say. Thanks!

Anonymous said...

Hi, I am Umesh, this is very nice article i find it & get solution so quickley.thanks for such nice article.

Anonymous said...

That was a big help - I had been trying on and off for ages to track down the jdbc-odbc bridge. I found lots of sites explaining its use in programs but nothing that told me *where* I could find it (except that it was installed with the Sun jre).
However, when I try to load rt.jar into BIRT, it sits there for a long time and then dies (log shows “out of memory error: PermGen space”). This is on two pretty well spec’d boxes (eg 2GB ram, 3GHz P4).
I then unzipped the jar file and extracted the sun\jdbc\odbc tree and zipped it up. Using that has enabled me to connect to Access, following your instructions
Thanks a lot for that.
Rgrds
Peter

Anonymous said...

Wow thanks for this post! It is really helpful.. best regards, nikolai

Anonymous said...

Nice! Got it to work without a hassle!

Anonymous said...

It is very nice instruction to connect to MS Access databse via JDBC

Anonymous said...

Thank you very much!

Anonymous said...

Works perfectly, Thanks!!

citizenX said...

great, i've spent ages looking at how to integrate access with BIRT, unfortunatly my work place still hoild the in accurate view that maccess/excel is the bee's knees hence i'm forced to intergrate BIRT with access. Thank you very much for the TIP

Unknown said...

Thank you very much.
Very nice and detailed tutorial

Anonymous said...

Works perfectly, Thanks!!

Anonymous said...

Excellent document. I was able to use it to create a JDBC-ODBC connection to an Orable database.

Carlos said...

Absolutely simple and effective, this should be on the first page of documentation for beginners... thanks a lot for sharing

Anonymous said...

Thanks so much!!!!

Anonymous said...

Thanks , keep up the good work...its very helpful for beginners !!!

Unknown said...

Hi,
I have a problem whene I add new data source and I click in JDBC Data Source and click on next... the window crashes and eclipses does not respond
any ideas?