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.

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 Administrator. This 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)
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