Connecting JSP page with MS Access :: JDBC ODBC Connectivity

Hello Guys .This post is all about Connecting JSP to MS Access database -> JDBC ODBC Connectivity.

Honestly speaking after struggling upon a lot on jdbc connectivity , finally at the end of the day , I brought to you the step by step process to CREATE  database and perform DDL and DML operations on that.

So , Lets begin by creating a MS Access database.

Go to Control Panel > Administrative Tools > ODBC DATABASE SOURCES. Now an ODBC Data   Source Administrator window will open. Click on Add button .

Now Select Driver do Microsoft Access(*mdb) from the Create New Database Source window that appears like the image shown on right and click Finish Button..

Now create your Database source name . You can give any name you want. THIS NAME IS CALLED YOUR DNS ( Database Sourcse Name ). Click on "Create " button.

NOW THIS STEP IS Worth IMPORTANT. PEOPLE USUALLY GIVES C:\ DRIVE  PATH ( the NTFS where windows is installed generally).. There's no problem but on some computers / windows version this POSE A SECURITY PERMISSION PROBLEM SO WHATSOEVER CORRECT PROGRAM YOU WRITE YOU WILL GOT AN EXCEPTION BECAUSE IT MAKES DSN READ-ONLY SO EXCEPTION OCCURS. So to rectify this choose other drive for database  creation .Now once created choose Select Button and select the database path you just created.This is it ! your DNS IS CREATED.

Now come on to program .Here's a sample program .

<%@page import="java.sql.*"%>

<%@ page import ="javax.sql.*" %>

<%String database="e:\\xampp\\tomcat\\webapps\\ROOT\\lms\\lmshare.mdb";

String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + database + ";"; 

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection(url,"","");

Statement stmt=con.createStatement();
// CREATION 
String create="create table lmsusers(id integer ,email varchar(20), pwd varchar(20),fname
varchar(20),lname varchar(20),age integer) ;"; stmt.executeUpdate(create);

//INSERTION
String adduser="insert into lmsusers(email,pwd,fname,lname,age) values ('"+name+"', '" +pwd+"','"+fn+"','"+ln+"',"+age
+");";
stmt.executeUpdate(adduser);

out.println("connected");%>

Now String database="e:\\xampp\\tomcat\\webapps\\ROOT\\lms\\lmshare.mdb"; is  the path to database Source Name you just created .Notice I have used e drive for the security problems I just discussed.
String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + database + ";";  defines Ms Access and initializes its driver.- a significant step.

To create database use String create="create table lmsusers(id integer ,email varchar(20), pwd varchar(20)
,fname varchar(20),lname varchar(20),age integer) ;";
-which is self understandable code.To insert values use the insertion operation as shown in the sample program.
Likewise you can perform any DDL / DML OPERATION(s). But worth note the security permission problem that is discussed above.So what are you waiting for just save page with name say sample.jsp and  save it at \\tomcat\webapps\ROOT\ folder and run tomcat and check the result Enjoy!!.