JDBC Connection to MS-Access in Windows 7

In this article, we explain how a Java program can connect to a Microsoft Access Database.

JDBC connection to Microsoft Access in Windows 7

 

Introduction 

 
In this article, we make a connection using JDBC to a Microsoft Access database. This connection is made with the help of a JdbcOdbc driver. You need to use the following steps for making the connection to the database.
 

Creating a Database

 
Step 1: Open Microsoft Access and select  Blank database option and give the database name as File name option
 
i0.jpg 
 
Step 2: Create a table and insert your data into the table
 
i1.jpg 
 
Step 3: Save the table with the desired name; in this article, we save the following records with the table name student.
 
i2.jpg 
 

Now Creating DSN of your database

 
Step 4: Open your Control Panel and then select Administrative Tools.
 
Step 5 : Click on Data Source(ODBC)-->System DSN.
 
dsn1.jpg 
 
Step 6: Now click on add option for making a new DSN.select Microsoft Access Driver (*.mdb. *.accdb) and then click on Finish
 
dsn2.jpg 
 
Step 7: Make your desired Data Source Name and then click on the Select option, for example in this article we use the name mydsn
 
dsn3.jpg
 
 
Step 8: Now you select your data source file for storing it and then click ok and then click on Create and Finish
 
dsn4.jpg 
 
Step 9: Java program code
  1. import java.sql.*;  
  2. public class MsAcessODBC {  
  3.     public static void main(String[] args) {  
  4.         try {  
  5.             // loading thejdbc odbc driver  
  6.             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  7.             // creating connection toth data base  
  8.             Connection con = DriverManager.getConnection("jdbc:odbc:mydsn""""");  
  9.             Statement st = con.createStatement();  
  10.             // create an execute sql command on database      
  11.             ResultSet rs = st.executeQuery("Select * from student order by rollno asc");  
  12.             ResultSetMetaData rsmd = rs.getMetaData();  
  13.             // this getColumnCount reurn the number of column in the selected table  
  14.             int numberOfColumns = rsmd.getColumnCount();  
  15.             // while loop and with while loop code use for print the data  
  16.             while (rs.next()) {  
  17.                 for (int i = 1; i <= numberOfColumns; i++) {  
  18.                     if (i > 1)  
  19.                         System.out.print(", ");  
  20.                     String columnValue = rs.getString(i);  
  21.                     System.out.print(columnValue);  
  22.                 }  
  23.                 System.out.println("");  
  24.             }  
  25.             st.close();  
  26.             con.close();  
  27.         } catch (Exception ex) {  
  28.             System.err.print("Exception: ");  
  29.             System.err.println(ex.getMessage());  
  30.         }  
  31.     }  
  32. }  
OUTPUT
 
cmd.jpg 
 
Resource