Accessing Ms Access Database with Java

Microsoft Access is a software database which still has been used until now. This database is very light, so this is an advantage for it’s user to chose this type of database in order to save the data. Microsoft Access is also come with a lot and usable feature. Starting from creating table, form, queries, report, and even the macros, everything became simple when we want a do it on Microsoft Access, and its typically often that this Microsoft Access is used by everyone to make a database application where the data will be save on its self.

We will not discuss a lot about what Microsoft Access can do here. On this time we will try to accessing a database that created using Microsoft Access with our beloved programming language which is Java.

Now, let’s arrange a scenario to discuss this topic:

  1. Create a database file named Java_MsAccess using Microsoft Access.

  2. Create a table on that database which contains fields:

  • Field name: CustomerID, type Number (single).

  • Field name: CustomerName, type Text (50).

  • Field name: CustomerAddress, type Text (250).

  • Field name: CustomerPhone, type Text (20).

  1. Fill up the table with necessary data.

  2. Create a DSN Data source DSN for this database.

  3. Create a Java Application for accessing this database using the Data source.

For the step 1 until the step 4, we will attach a screen shot only here, because I believe that we can do these steps without written reference here.

Okay, for the 1st step, below is the screen capture:

createmsaccessdb

And for the 2nd step, below is screen capture:

createmsaccessdb

 

And for the step 3rd, this is how we do it:

fillupmsaccesstable

And for the step 4th, okay for this step we will explain a little bit, so we can understand how to create a data source on windows platform for this database.

  • Click start Control Panel Administrative Tools Data Sources(ODBC)

  • And then on ODBC Database Administrator window, click Add button and then choose Driver do Microsoft Access (*.mdb), and then click finish button.

  • And then on ODBC Microsoft Access Setup window, give a name for this database JavaMsAccess, and then choose the database file by clicking select button, and find the database file that we have crated before. This is the image looks like:

createodbcdsn

And the DSN data source named by JavaMsAccess created successfully. Now we arrive to the step that we are cannot be patient to wait which is creating the Java application. Okay, here’s the process flow of our Java Application looks like:

  1. Choose and initialize the driver for this ODBC data source.

  2. Create a database connection to this ODBC data source.

  3. Create a Statement for the SQL Queries.

  4. Pick up the result of Query Statement executiom onto ResultSet

  5. Printout the ResultSet.

From these scenarios, let’s implement it on to Java Code. Take a look this Java source code below:

package org.mojo.blog.Java_depth.aboutMsOffice;

import Java.sql.*;

/**
* Created by IntelliJ IDEA.<br>
* User: Mojo<br>
* Date: 08 Feb 09<br>
* Time: 22:30:08<p>
*/
public class MsAccessJava {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private static String url = "jdbc:odbc:JavaMsAccess";
 public static void main(String[] args) {
     String sql ="SELECT * from tblCustomer";
       try {
          Class.forName(driverName);
          Connection conn = DriverManager.getConnection(url);
          System.out.println("Connection Established..");
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while (rs.next()){
             System.out.println("+------------- Detail Customer ----------------------+");
             System.out.println("Customer ID : "+ rs.getInt(1));
             System.out.println("Customer Name : "+ rs.getString(2));
             System.out.println("Customer Address : "+ rs.getString(3));
             System.out.println("Customer Phone : "+ rs.getString(4));
             System.out.println("+----------------------------------------------------+");
             System.out.println("\n");
         }
       } catch (SQLException e) {
          e.printStackTrace();
       } catch (ClassNotFoundException e) {
          e.printStackTrace();
     }
  }
}

Let’s discuss line per line the above code.

The first line is a line where this Java file located on its package. While the 3rd line is a line where all the classes of Java.sql library has imported. And then we can see inside the program that there are two private static string variable defined which is:

………..
   private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
   private static String url = "jdbc:odbc:JavaMsAccess";
………..

The first variable is a driver name that we will use it later to accessing ODBC data source, while the second variable is an url of the data source that we would like to access, just remember this data source name have to equals with the data source that we have created before which is: JavaMsAccess.

And then let’s take a look the codes inside the main method. The first line on this main method is a line where we define a SQL query which we will use to access these Microsoft Access database. And then we create a try-catch block, this block is the block which all main process of the accessing process is happen. First in this block we initialize the driver that we have defined in the header of the program. And then we create a database connection and the print out the result of it’s connection.

…….
   Class.forName(driverName);
   Connection conn = DriverManager.getConnection(url);
   System.out.println("Connection Established..");
……….

The continuous steps are, create a statement, then execute the SQL Statement using the SQL query that we have defined before.

…….
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql);
……….

And the print out the result by loops the rs variable, which these ResultSet variable values had filled up before.

…….
   while (rs.next()){
        System.out.println("+----Detail Customer ------------------+");
        System.out.println("Customer ID : "+ rs.getInt(1));
        System.out.println("Customer Name : "+ rs.getString(2));
        System.out.println("Customer Address : "+ rs.getString(3));
        System.out.println("Customer Phone : "+ rs.getString(4));
        System.out.println("+--------------------------------------+");
        System.out.println("\n");
   }
…….

And then we add some catch statement to prevent error appear on runtime, and this catch is also can be use to trace the problem when error occured that can make our application cannot run properly.

If the program executed, and then the result will show just like image below – i have executed it on my favourite IDE, IntelliJidea 7.0.4

executeodbc_java

That’s it, that’s all our try out has finished. Please try and modify for your own need and your own desire.


Hope this will usefull

Menteng, 09th February, 2009


josescalia

2 thoughts on “Accessing Ms Access Database with Java

  1. Pingback: Accessing Ms Access Database with Java | XML Developer

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s