Creating table and accessing data from MS-Access in JDBC

After creating the connection as we discussed in 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 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 database in MS-Access.
  
   
  

After 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 Administrator. This ODBC was originally created to provide an API standard for SQL on Windows plateform and was later enhanced to provide sdk for other plateform.

ODBC defines a collection of functions for the direct access to data without the need of an embedded sql in client application. These functions were defined by Microsoft,and implentation of those function were given by specific vendor(Oracle,Sybase etc).

Creating 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) 
  
  
  
  
• A window, ODBC Administrator, will open then select the System DSN menu and click Add button
  
  
  
  
• A new window, Create a new Data Source will open then select the Microsoft Access Driver, as shown below
  
  
  
  
• After this a new window ODBC MicroSoft Access Setup will open then write the dsn and select  the Select button
  
  
  
  
• A new window Select Database will open select your database and click on OK button
  
  
  

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

        

import java.sql.*;
public class Employee15
{
    public static void main(String[] args)
   {
        try
        {   
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:jdbcdsn", "","");
            Statement s = con.createStatement();
            s.execute("create table employee ( emp_id number,emp_name varchar(20),emp_address varchar(30) )"); // create a table
            s.execute("insert into employee values(001,'ARman','Delhi')"); // insert first row into the table
            s.execute("insert into employee values(002,'Robert','Canada')"); // insert second row into the table
            s.execute("insert into employee values(003,'Ahuja','Karnal')"); // insert third row into the table
            s.execute("select * from employee"); // select the data from the table
            ResultSet rs = s.getResultSet(); // get the ResultSet that will generate from our query
            if (rs != null) // if rs == null, then there is no record in ResultSet to show
            while ( rs.next() ) // By this line we will step through our data row-by-row
           {
                System.out.println("________________________________________" );
                System.out.println("Id of the employee: " + rs.getString(1) );
                System.out.println("Name of employee: " + rs.getString(2) );
                System.out.println("Address of employee: " + rs.getString(3) );
                System.out.println("________________________________________" );
           }
                s.close(); // close the Statement to let the database know we're done with it
                con.close(); // close the Connection to let the database know we're done with it
        }
        catch (Exception err)
            {
                 System.out.println("ERROR: " + err);
            }
      }
}

After executing this program a table employee with three records is created

          

Output window