Creating Table and Accessing Data From MS-Access in JDBC

Introduction 

 
After creating the connection as we discussed in an earlier article, "Introduction to Jdbc," we create a table and execute queries so that we can frequently access to the database and perform CURD(Create, Update, Retrieve & Delete) operation on any table. Now, first of all, we have to create a database in MS-Access.
 
Creating a database in MS-Access
 
To create a database in MS-Access, we have to perform the following step:
  • Open MS-Access and select the new database. The figure given below will help you to create a database in MS-Access. 

     database in MS-Access       
After the creation of a database, we have to create a datasource name and we have to add our database to that data source. This data source is known as ODBC(Open Database Connectivity) Data Source AdministratorThis ODBC was originally created to provide an API standard for SQL on Windows platform and was later enhanced to provide SDK for other platforms.
 
ODBC defines a collection of functions for direct access to data without the need for an embedded SQL in the client application. These functions were defined by Microsoft and implementation of those function were given by a specific vendor(Oracle, Sybase, etc).
 
Creating a data source name and adding our database to that datasource name
 
Different step for creating DSN (datasource name) and adding it to our database.
  • Go to the Control Panel and select the Administrative Tool 
  • Select and open the Data Sources (ODBC)  
     
    odbc in administrative tool
      
  • A window, ODBC Administrator, will open then select the System DSN menu and click Add button  

    system dsn in Odbc        
  • A new window, Create a new Data Source will open then select the Microsoft Access Driver, as shown below  

    adding MS-Access Driver        
  • After this a new window ODBC Microsoft Access Setup will open then write the DSN and select  the Select button  

    writing the data source name        
  • A new window Select Database will open select your database and click on OK button  

    selecting the database in dsn       
Now we are ready to create any table and insert the values in that table.
 
Simple program for creating a table employee and inserting the value in the table
 
Before executing the program the MS-Access is  
Access database without any table
  1. import java.sql.*;  
  2. public class Employee15  
  3. {  
  4.     public static void main(String[] args)  
  5.    {   
  6.         try   
  7.         {      
  8.             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  9.             Connection con = DriverManager.getConnection("jdbc:odbc:jdbcdsn""","");  
  10.             Statement s = con.createStatement();  
  11.             s.execute("create table employee ( emp_id number,emp_name varchar(20),emp_address varchar(30) )"); // create a table  
  12.             s.execute("insert into employee values(001,'ARman','Delhi')"); // insert first row into the table   
  13.             s.execute("insert into employee values(002,'Robert','Canada')"); // insert second row into the table   
  14.             s.execute("insert into employee values(003,'Ahuja','Karnal')"); // insert third row into the table   
  15.             s.execute("select * from employee"); // select the data from the table  
  16.             ResultSet rs = s.getResultSet(); // get the ResultSet that will generate from our query  
  17.             if (rs != null// if rs == null, then there is no record in ResultSet to show  
  18.             while ( rs.next() ) // By this line we will step through our data row-by-row  
  19.            {   
  20.                 System.out.println("________________________________________" );  
  21.                 System.out.println("Id of the employee: " + rs.getString(1) );  
  22.                 System.out.println("Name of employee: " + rs.getString(2) );  
  23.                 System.out.println("Address of employee: " + rs.getString(3) );  
  24.                 System.out.println("________________________________________" );  
  25.            }  
  26.                 s.close(); // close the Statement to let the database know we're done with it  
  27.                 con.close(); // close the Connection to let the database know we're done with it  
  28.         }  
  29.         catch (Exception err)   
  30.             {  
  31.                  System.out.println("ERROR: " + err);  
  32.             }  
  33.       }  
  34. }  
After executing this program a table employee with three records is created:
 
table in MS-ccess  
 
Output window
 
output of table in MS-Access