Hello Engineers,

I have seen candidates struggling a lot while making a first time connection to a database(Oracle, MS access etc) using Java or any other front end technology. That is why I have added this article to make it simple and fast.

In this article we will learm how to connect to MS access database(*.accdb, *.mdb) using java program.

Before conneting to MS access database, you need to create data source in Control panel. Follow this llink to create data source in Control Panel >>Administrative Tools for MS access database.

Once the data source is created, we will move to the coding part. Here I am presenting the least and simplest code to connect to MS Access database.

We will try to understand the code step by step.

1. Ignore the imports as they are generated automatically.
2. We have created a class for the name "sun.jdbc.odbc.JdbcOdbcDriver".
3. We have created a Java.sql.connection to connect to the ODBC driver. It uses getConnection of DriverManager Class.
4. In getConnection method we have to specify the connection string.
5. The connection string in this case is "jdbc:odbc:<data-sourcename>". The data source name is same as the name given while creating the data source in Administrative Tools>>Data sources.

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
public class Apples{

  public static void main(String[] args) {   
    try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con= DriverManager.getConnection("jdbc:odbc:InstantMsg");    
    Statement st=con.createStatement();
    ResultSet rs=st.executeQuery("Select * from logintbl");
    while(rs.next()){
     System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
    }    
    st.close();
con.close(); 
   } catch (Exception e) {
    // TODO Auto-generated catch block
    System.out.println(e.getMessage());
   }   
  }
}

1. Then we have created a statement java.sql.Statement using createStatement method of connection object "con".
2. To get the results returned from executed query, we have to dreate a result set rs, so the result set rs will store the data fetched from the executeQuery method od statement object.
3. Finally we have used a while loop to get all the fetched data and printed it on console by rs.getString(number); here the number is the column number of the retuned data from query.
4. So, if below is the table logintbl in our database (*accdb, *.mdb) file with two or more columns, the out put of the program should give the first column and seconds column values.

The output is:

1 shankar kumar

2 sagar raut

3 priyanka das

Now, we shall see the JDBC connectivity between front end Java and back end as Oracle database.

Here shankar is the data source name. Similarly, you will have to create a data source for Oracle database. It is similar to Follow this llink to create data source in Control Panel >>Administrative Tools for MS access database

Only differece is you have to select driver as "Microsoft ODBC for Oracle" and click on Finish.

Then enter data source name, give username, password additionally.

Click here to learn how to create data source for driver MS ODBC Oracle

Here we are using Oracle 9i as back end. Here if you notice, the only differece is in connection string. The format of connection string is:

Connection con = DriverManager.getConnection(Url as string,username as string,password as string);

Here the url used is : jdbc:odbc:shankar

/********* Back-end as Oracle 9i *******************/

   try  {
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  Connection con = DriverManager.getConnection("jdbc:odbc:shankar", "scott","tiger");
  Statement st = con.createStatement();
  ResultSet rs=st.executeQuery("Select * from logintbl");
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}   
  st.close();
  con.close();
         }
 catch (Exception ex)
  {
  System.out.println(ex);
  }